Posts
Internal Error ORA-600[QXIDMACLOB]
Export/Import Errors:
We have automated script running daily to do export database for the development server, but to my strange I find Internal Error ORA-600[QXIDMACLOB].
Well this case is repeating from couple of days and I could not able to identify this but latter followed up to metal ink where I could find the correct solution for this
Cause
The error is related to Bug:1805146 and is caused by a data dictionary inconsistency left by the ALTER TABLE SET UNUSED statement.This statement mark/delete entries in the col$ table related to that object but, miss or fail to mark/delete rows in some tables called coltype$, lob$ and partlog$.Causing the inconsistency.
Solution
Use the following workaround:After drop a column, this is marked as UNUSED.In order to avoid this error, customer must drop al unused columns executing the following command:ALTER TABLE DROP UNUSED COLUMNS;If the table is really big, this statement can potentially use a lot of UNDO space.In order to avoid UNDO space errors, customer must use the following statement:ALTER TABLE DROP UNUSED COLUMNS checkpoint 100;Checkpointing cuts down the amount of undo logs accumulated during the drop column operation to avoid running out of rollback segment space.If you do not specify a number (integer) , Oracle sets the default of 512.
*** I tried working on this and could able to solve the problem.
We have automated script running daily to do export database for the development server, but to my strange I find Internal Error ORA-600[QXIDMACLOB].
Well this case is repeating from couple of days and I could not able to identify this but latter followed up to metal ink where I could find the correct solution for this
Cause
The error is related to Bug:1805146 and is caused by a data dictionary inconsistency left by the ALTER TABLE SET UNUSED statement.This statement mark/delete entries in the col$ table related to that object but, miss or fail to mark/delete rows in some tables called coltype$, lob$ and partlog$.Causing the inconsistency.
Solution
Use the following workaround:After drop a column, this is marked as UNUSED.In order to avoid this error, customer must drop al unused columns executing the following command:ALTER TABLE DROP UNUSED COLUMNS;If the table is really big, this statement can potentially use a lot of UNDO space.In order to avoid UNDO space errors, customer must use the following statement:ALTER TABLE DROP UNUSED COLUMNS checkpoint 100;Checkpointing cuts down the amount of undo logs accumulated during the drop column operation to avoid running out of rollback segment space.If you do not specify a number (integer) , Oracle sets the default of 512.
*** I tried working on this and could able to solve the problem.
Hi Prakash,
ReplyDeleteI have a doubt in your blog see since i am new to oracle dba please clarify my doubt.in the below article you specify that alter table drop unused columns will use lot of undosspace since alter is a ddl how the data in that table will go in undo tablespace.
Thanks and Regards
Prakash
Use the following workaround:After drop a column, this is marked as UNUSED.In order to avoid this error, customer must drop al unused columns executing the following command:ALTER TABLE DROP UNUSED COLUMNS;If the table is really big, this statement can potentially use a lot of UNDO space.In order to avoid UNDO space errors, customer must use the following statement:ALTER TABLE DROP UNUSED COLUMNS checkpoint 100;Checkpointing cuts down the amount of undo logs accumulated during the drop column operation to avoid running out of rollback segment space.If you do not specify a number (integer) , Oracle sets the default of 512.