Oracle ORA-08104 error handling method and precautions

"Introduction to the Environment"

System environment: IBM P740 8205-e6c (AIX) + 11.2.0.3.0 Oracle RAC

"Introduction to the Background"

Fault Description: The database table space is more than 90%, unable to expand the table space, need to clean up the historical data of the business side, the ORA-08104 error occurred during the cleanup, no more cleanup data.

Feedback error message:

The error from the database indicates that a session has performed a rebuild index operation or a fallback when the index operation is rebuilt.

"Problem Analysis"

View database log information: found the database error, a temporary table space shortage, view the current table is a partitioned table, the size of more than 180 g, suspected of rebuilding the index when the temporary table space is insufficient to cause rollback.

View Wait Event information: No exception wait events were found.

View a session to index this object: Based on the user's handsome selection, it is found that the active state session does not have an index operation on the object.

See if there is a large transaction rollback condition: The query does not have large transaction rollback information.

To see if a zombie process: based on the name of the object, locate sqltext,sql_id discovers that there is a inactive status session with the Rebuild index operation

After consulting, the demand side feedback during the indexing operation on the object, but during the terminal terminal, then re-delete the partition times wrong.

Confirm that the session can be killed, the current status is inactive state, the SPID will need to locate the host process to kill, kill to do after the need to clean up the index information, or will be error.

Declare
Done Boolean;
Begin
Done:=dbms_repair.online_index_clean (275314);---object_id
End
/

Drop Partition Failed with Ora-08104:this Index Object is Being Online Built Or Rebuilt (document ID 2358693.1)

Summary

Although the manipulation is very simple, but the attention of the problem will be many, accurate evaluation in order to make the normal completion of the project:

When deleting data, you need to back up data (physical backup, logical backup), make proper cleanup strategy, move or clean up the historical data regularly, prevent the data loss caused by misoperation;

For the Log Type monitoring table, it is recommended to use partition method to store data, easy to clean up historical data, SQL partition query performance optimization;

For global indexes It is recommended that the index be rebuilt using online mode when the table is smaller;

When rebuilding an index, note the size of the table and the temporal table space, and it is recommended to use a parallel approach to rebuild the index for larger objects;

It is recommended to use a script to remove or rebuild the index operation to prevent the network from causing the terminal disconnection to cause transaction rollback;

It is recommended to collect statistics according to the partition after the table operation.

It is recommended that you use local index for partitioned tables, and global index delete partitions cause index invalidation.

Oracle ORA-08104 error handling method and precautions