After a cleanup recovery procedure using Online switch, there are still certain objects that remain uncleaned. How can I tell which table belongs to which reorg job after a cleanup using Online switch?
If after running the clean up scripts, there is still one set that remains, for example: The Quest tables space is at 98% and the dev_cp log file has numerous entries trying to extent the tablespace.
You can issue: SQL> select object_type,owner,object_name from dba_objects where object_name like 'QUEST_QSA_LW%'; sample output: OBJECT_TYPE OWNER ------------------ ------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- TABLE QUEST QUEST_QSA_LWDATA39461 INDEX QUEST QUEST_QSA_LWIX39461 TRIGGER QUEST QUEST_QSA_LWRT39461
And from a tablespace path: 10069541 ORA-1654: unable to extend index QUEST.QUEST_QSA_LWIX39461 by 1024 in tablespace PSAPQUEST 10069542 ORA-1654: unable to extend index QUEST.QUEST_QSA_LWIX39461 by 1024 in tablespace PSAPQUEST
If the user wishes to know what table this was for, this will work if it was an Online switch: Select object_type,owner,object_name from dba_objects where object_id= 39461;
Note: Please make sure that there are no LiveReorg jobs running and there are no jobs that the user will re-start, then you can delete all objects from the database that have an owner of QUEST and match the object name 'QUEST_QSA_LW%'. Also, you can use SpaceManager -> tablespace map to look at the tablespace to make verify if it is empty.
