Connect with SPACE MANAGER USER while dropping the quest temporary tables.
Run below queries to generate drop statements for quest user and after validation, execute them manually:
1. Generate Drop statements for triggers on QUEST temporary table.
SQL> select 'drop '||object_type||' '||OWNER|| '."'||object_name||'";' from dba_objects WHERE OBJECT_NAME LIKE 'QUEST_QSA_LW%_%' and OBJECT_TYPE='TRIGGER';
2. Generate Drop statements for QUEST tables:
SQL> select 'drop '||object_type||' '||OWNER|| '."'||object_name||'";' from dba_objects WHERE OBJECT_NAME LIKE 'QUEST_QSA_LW%_%' and OBJECT_TYPE='TABLE';
SQL> select 'drop '||object_type||' '||OWNER|| '."'||object_name||'";' from dba_objects WHERE OBJECT_NAME LIKE 'QUEST_SPC_TMP%_%';
3. Generate Drop statements for %_ORIG table
Note: For later reference or investigation you can keep %_ORIG table during a period of time, this is backup copy of your master table it will not contain any database objects like index, view, trigger etc. Before you dropping the tables confirm that you are dropping %_ORIG tables only not the master tables.
SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE '%_ORIG';
4. Confirm that all related indexes are gone. Normally after dropping temporary tables, the dependent index will also get dropped, but still please cross-check it.
SQL> select 'drop '||object_type||' '||OWNER|| '."'||object_name||'";' from dba_objects WHERE OBJECT_NAME LIKE 'QUEST_QSA_LW%_%' and OBJECT_TYPE='INDEX';
5. Drop sequences
SQL> select 'drop '||object_type||' '||OWNER|| '."'||object_name||'";' from dba_objects WHERE OBJECT_NAME LIKE 'QUEST_QSA_LW%_%' and OBJECT_TYPE='SEQUENCE';
6. Drop procedures
SQL> select 'drop '||object_type||' '||OWNER|| '."'||object_name||'";' from dba_objects WHERE OBJECT_NAME LIKE 'QUEST_QSA_LW%_%' and OBJECT_TYPE=' PROCEDURE';
Now you’re ready to run the cleanup utility on the Space Manager to remove the job.
