This error is received seconds after restarting a cancelled reorg:
Restart for table type TABLE
Restarting job number 53 53
Original for PRSNL.5101601637, job 53 requires object ID validation for restart
Error retrieving index XPKPRSNL for table PRSNL: ORA-01403
ORA-01403: no data found
AbortScript()
AbortScript: Aborted QSA-20392 Internal error in script engine at lw_oracle.c:494 (os errno 13)
trigger locks were still in place, users were attempting to update the reorg eventually changing the database state leading to inconsistency as seen in the log
"...error retrieving index...ORA-01403: no data found..."
Caution should be exercised when cancelling a job, note the stage of the reorg and if cancelling during a switch it may be necessary to drop tlocks (if any exist) to release the object for access by users. Then cleanup any temporary objects once it has been confirmed that the reorg can't be restarted:
1. For online reorg, remove and delete triggers:
select object_name, object_type from dba_objects where object_name like '%TREP%';
2. For livereorg with tlock switch remove tlocks:
select object_name, object_type from dba_objects where object_name like '%TLOCK%';
3. Remove space manager work objects:
Look for objects with names starting with QUEST_QSA_ and containing the object id of the table, and drop those using SQL statements.
e.g. if the table object id is 12345, you can find the names of the temporary objects to drop with this query:
select owner, object_name, object_type from dba_objects where object_name like 'QUEST\_QSA\_%12345' escape '\' ;
In addition, if the table being reorganized has a LOB column, then you will want to drop the materialized view log that Space Manager created on the table being reorganized:
drop materialized view log on OWNER.TABLE_NAME ;
In version 6.5 and above the manual cleanup above can be replaced by clicking the cleanup button against the failed job in the job monitor screen.
