Imagine running an online index rebuild on an Oracle 19c database, only to have it unexpectedly fail. No rollback in progress, no active session, just an error leaving your index in limbo. That’s exactly what happened to me last week, and after some digging, I discovered an interesting behavior involving Oracle’s System Monitor Process (SMON). If you’ve ever encountered ORA-08104 or ORA-12805, this post will walk you through what’s happening behind the scenes—and how to speed up the fix.
Context
This happened on an Oracle 19.21 database. I was rebuilding an index when, unexpectedly, the session was terminated within seconds.
Command sample executed:
ALTER INDEX "OWNER_SAMPLE"."INDEX_SAMPLE" REBUILD TABLESPACE TBS_SAMPLE COMPRESS ADVANCED HIGH ONLINE PARALLEL 2;
*
ERROR at line 1:
ORA-12805: parallel query server died unexpectedly
At first, I only saw ORA-12805, but after checking the alert log, I discovered that an ORA-07445 had also occurred, the complete error is the following and it is still under investigation in a SR
ORA-07445: exception encountered: core dump [kdil_copy_cu_to_block()+545] [SIGSEGV] [ADDR:0x7F1BB2B37619] [PC:0xA292C71] [Address not mapped to object] []
Issue is related with advanced compression using HIGH for indexes but anyway, the post is not about the ORA-07445 or compression it is about something else!
After the failure, I tried a couple of things to identify the cause, as I wasn’t sure what went wrong.
SQL> ALTER INDEX "OWNER_SAMPLE"."INDEX_SAMPLE" REBUILD TABLESPACE TBS_SAMPLE ONLINE;
ALTER INDEX "OWNER_SAMPLE"."INDEX_SAMPLE" REBUILD TABLESPACE TBS_SAMPLE ONLINE
*
ERROR at line 1:
ORA-08104: this index object 14122185 is being online built or rebuilt
and got that message: ORA-08104: this index object 14122185 is being online built or rebuilt
So, nothing was actually running, no rollback in progress, no session or process id running in the OS, I was digging into trying to find out the reason
After waiting around 45 minutes to an hour, I suddenly found that I could successfully run the rebuild again. However, at that moment, I couldn’t pinpoint exactly what had triggered the cleanup and allowed the operation to proceed
I was quite curious about it, and I wanted to continue with the test, I connected to a restore of the same database (lower environment) and I ran the first command to reproduce the issue and hit the bug in the alert and got the ORA-08104 in my session again!
With no active rollback process and nothing running, I started investigating how Oracle internally handles failed index rebuilds. That’s when I took a closer look at the System Monitor Process (SMON).
How SMON handles failed index rebuilds
Oh yes, we need to talk about the SMON!.
SMON is responsible for a variety of system-level cleanup tasks. Key functions of SMON include:
- Instance Recovery: If the database experiences an unexpected shutdown or crash, SMON automatically performs instance recovery upon restart. This process involves rolling forward changes from the redo logs and rolling back uncommitted transactions to maintain data consistency.
- Space Management: SMON cleans up temporary segments that are no longer in use, reclaiming space and preventing fragmentation within tablespaces. Example: Oracle Database allocates extents when creating an index. If the operation fails, then SMON cleans up the temporary space.
- Coalescing Free Space: In dictionary-managed tablespaces, SMON coalesces contiguous free extents to optimize space utilization and reduce fragmentation.
- Recovery of Failed Processes: If a user process fails, SMON is responsible for cleaning up the database buffer cache and freeing resources that were allocated to the failed process.
So SMON fixed the problem but how? well
Since Oracle versions 12.1.0.2 and later, an internal job named CLEANUP_ONLINE_IND_BUILD runs hourly to clean up temporary segments left by failed online index build operations, and there is something else that fixes the data dictionary (continue reading =D ), which may incorrectly indicate that a rebuild is still in progress when it’s not.
Speeding up the index cleanup process
I don’t want to wait until the cleanup process run again, good news! We have a manual way to speed up the process and retry the command.
You can retrieve the object ID from the error message when the failure occurs, optionally, you can use the following query to identify objects that need cleanup:
SELECT i.obj#, i.flags, u.name, o.name, o.type#
FROM sys.obj$ o, sys.user$ u, sys.ind$ idx, sys.ind_online$ i
WHERE bitand(i.flags, 512) = 512 and o.obj#=idx.obj# and
o.owner# = u.user# and idx.obj#=i.obj#;
Manually cleaning up with DBMS_REPAIR.ONLINE_INDEX_CLEAN
DBMS_REPAIR.ONLINE_INDEX_CLEAN function performs a manual cleanup of failed or interrupted online index builds or rebuilds.
This action is also performed periodically by SMON, regardless of user-initiated cleanup.
This function returns TRUE if all indexes specified were cleaned up and FALSE if one or more indexes could not be cleaned up.
Syntax:
DBMS_REPAIR.ONLINE_INDEX_CLEAN (
object_id IN BINARY_INTEGER DEFAULT ALL_INDEX_ID,
wait_for_lock IN BINARY_INTEGER DEFAULT LOCK_WAIT)
RETURN BOOLEAN;
Once you have the object index id you can call the DBMS_REPAIR.ONLINE_INDEX_CLEAN package as follow:
set serveroutput on
DECLARE
lv_ret BOOLEAN;
BEGIN
lv_ret := DBMS_REPAIR.ONLINE_INDEX_CLEAN(14122185);
--dbms_output.put_line(lv_ret);
END;
/
You can read more about the package in the following link: ONLINE_INDEX_CLEAN Function
There is also a useful MoS note that referred to the issue:
MoS Note: Session Was Killed During The Rebuild Of Index ORA-08104 (Doc ID 375856.1) To BottomTo Bottom
And this is what I did:
SQL> set time on
13:45:16 SQL> set timing on
13:45:18 SQL> ALTER INDEX "OWNER_SAMPLE"."INDEX_SAMPLE" REBUILD TABLESPACE TBS_SAMPLE NOCOMPRESS ONLINE;
ALTER INDEX "OWNER_SAMPLE"."INDEX_SAMPLE" REBUILD TABLESPACE TBS_SAMPLE NOCOMPRESS ONLINE
*
ERROR at line 1:
ORA-08104: this index object 14122185 is being online built or rebuilt
set serveroutput on
DECLARE
lv_ret BOOLEAN;
BEGIN
lv_ret := DBMS_REPAIR.ONLINE_INDEX_CLEAN(14122185);
--dbms_output.put_line(lv_ret);
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
13:46:02 SQL> ALTER INDEX "OWNER_SAMPLE"."INDEX_SAMPLE" REBUILD TABLESPACE TBS_SAMPLE COMPRESS ADVANCED LOW ONLINE ;
Index altered.
Conclusion
Errors like ORA-08104 and ORA-12805 during index rebuilds can be frustrating, but knowing how Oracle handles these situations helps you take control. SMON plays a crucial role in cleaning up temporary segments, and if you can’t afford to wait for the automatic fix, DBMS_REPAIR.ONLINE_INDEX_CLEAN is your best friend.
However, if you encounter ORA-07445 (core dump) or ORA-00600 (internal error), the issue could be deeper, potentially involving a database bug or corruption. In such cases, opening an SR with Oracle Support is strongly recommended to diagnose the root cause and prevent recurrence.
By staying informed and proactive, you can minimize possible issues and ensure database stability.
If you’ve encountered a similar issue, drop a comment below—I’d love to hear your experience and how you tackled it!
DatabaseVerse: Journey into the World of Databases 
Leave a comment