350: Aborted QSA-20391 ORA-01403 during internal SQL (4328749)

I was running multiple reorg jobs, T-Lock, each having approx. 1000 tables in each reorg script. Because of an issue with our tape management system not being able to offload our database offline redo logs, I had to cancel these multiple reorg jobs. Once the out TMS was back operational, I restarted the cancelled scripts up again.

Now that all the reorg jobs either finished successfully or terminated (as 3 did), I'm getting the following errors. The multiple reorg jobs were reorging tables grouped alphabetically (tables starting with N% in 1 reorg script, M% in another, O% in another etc., in my attempt to reorg all the tables out from the same tablespace and reorg them all into the same, new tablespace.

Sometimes when a job is cancelled you need to clean up and then restart.
1) There is a clean up button when you right-click the job in Job Monitor. Please use that feature to clean up the Reorg.
or
2) Try the manual clean up:
Manual cleanup steps consist of the following:

1) If a job fails inside the switch, you can'd do a cleanup or cancel the job using job monitor. In that case, you have to clean up manually. You go look in the reorg script, find the 'START_REORG' command FOR THAT TABLE, and then manuall run the DROP_OBJECT commands that are right before and right after the START_REORG. Sample of commands are:

begin
quest_spc_util.drop_object('SAPR3','TBTCO',null,'lw',false);
end;
/
begin
quest_spc_util.drop_object('SAPR3','QUEST_SPC_TMP_49201_LRTAB',

null,'table',false);
end;
/
begin
quest_spc_util.drop_object('SAPR3','QUEST_TREP_49201',null,'trep',false);
end ;

/

2) Confirm that there are no Space Manager triggers left on the original table (you can use Space Manager's Object Properties windows for that) - there should be no triggers with names starting with QUEST.

3) Make sure that there are no temporary objects left. The temporary objects should have been deleted because of what you did in step 1.
select * from dba_objects where object_name like 'QUEST\_SPC\_%LRTAB' escape '\' ;
select * from dba_objects where object_name like 'QUEST\_SPC\_%LRIDX' escape '\' ;

This query will show you objects used by any current Live Reorg. It will show you objects that belong to currently running jobs, or objects belonging to any jobs that aborted or were paused by the user.If no jobs are aborted or executing at this time, the queries should return 0 rows.

The manual cleanup is now complete.

NOTE:

Before you repeat a reorg, you have to make sure that the failed reorg has been purged of all temp objects before running the new reorg.
Additional info on what to do before repeating a reorg:
Caution should be exercised when cancelling a job, note the stage of the reorg, drop tlocks if any to release the object for users to access. Then cleanup any temporary objects if available once you have 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 ;