CREATE OR REPLACE VIEW reg_del_tables /* this view was created for bt5025, but can be used by anyone who needs to query the deleted schema and find the corresponding table in the regular schema. If a table in the regular schema does not have a table in the deleted schema (like a domain or extensible table), it won't show up here. The names of the regular and deleted schema are hard-wired into the view, so be sure and check the query to see if these are the correct names for your database. This view assumes that all tables in the deleted schema share the same name with their table in the regular schema with the addition of '_DEL'. To my knowledge, there are no standard tables that violate this naming convention. If you create a new table and violate this convention, your table will not show up here. Use it in good health. C. Fogelsong 07/26/2007 */ AS SELECT REG.table_name regtable, getpk(upper(reg.table_name)) regpk, del.table_name deltable from ALL_TABLES REG, ALL_TABLES DEL WHERE reg.table_name||'_DEL' = del.table_Name AND REG.owner = 'BIOTICS_USER' AND del.owner = 'BIOTICS_DEL'; CREATE OR REPLACE PROCEDURE clean_del(tablein IN varchar2) AS /* this stored procedure was done for BT5025. It uses the view REG_DEL_TABLES to check the rows of a table in the deleted schema against the corresponding table in the regular schema. Any records found to be in both schemas will cause the record to be deleted from the deleted schema table. This condition occurs when a record has been restored and not subsequently deleted from the deleted schema. Input parameter is the name of one specific table. Also, this procedure has the names of the regular and deleted schema users hard-wired into it. Check the SELECT and the DELETE statements to see if the user names need to be changed for your database. C. Fogelsong 7/26/2007 */ rtbl VARCHAR2(200); dtbl VARCHAR2(200); rpk VARCHAR2(200); TYPE rec_ids IS REF CURSOR; recs_to_be_del rec_ids; TYPE recs_to_be_deleted_list IS TABLE OF NUMBER; reclist recs_to_be_deleted_list; BEGIN DBMS_OUTPUT.ENABLE(1000000); -- DBMS_OUTPUT.PUT_LINE('tablein - '||tablein); SELECT regtable, regpk, deltable INTO rtbl, rpk, dtbl FROM reg_del_tables WHERE regtable = UPPER(tablein); --DBMS_OUTPUT.PUT_LINE(rtbl); --DBMS_OUTPUT.PUT_LINE(dtbl); --DBMS_OUTPUT.PUT_LINE(rpk); --look for records that are in deleted schema table that are also in regular schema table OPEN recs_to_be_del FOR 'SELECT '||rtbl||'.'||rpk||' FROM '||rtbl||', BIOTICS_DEL.'||dtbl||' WHERE '||rtbl||'.'||rpk||' = BIOTICS_DEL.'||dtbl||'.'||rpk; FETCH recs_to_be_del BULK COLLECT INTO reclist; CLOSE recs_to_be_del; FOR i in 1 .. reclist.COUNT LOOP --now delete the record in the deleted schema DBMS_OUTPUT.PUT_LINE('DELETING '||dtbl||' record id: '||reclist(i)); EXECUTE IMMEDIATE 'DELETE FROM BIOTICS_DEL.'||dtbl||' WHERE '||rpk||' = '||reclist(i); COMMIT; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Table '||tablein||' not found in deleted schema'); END; / CREATE OR REPLACE PROCEDURE clean_all_del AS /* this stored procedure was done for BT5025. It will check ALL tables in the deleted schema for records that are in both the deleted and regular schemas. Any records found to be in both schemas will cause the record to be deleted from the deleted schema table. This condition occurs when a record has been restored and not subsequently deleted from the deleted schema. There are no input parameters since this procedure will clean all of the tables in the deleted schema by calling the clean_del procedure one by one for each entry reg_del_tables view. C. Fogelsong 7/26/2007 */ CURSOR tab_cur IS SELECT rd.regtable FROM reg_del_tables rd; TYPE tables_in_del_schema IS TABLE OF VARCHAR2(30); tablist tables_in_del_schema; BEGIN DBMS_OUTPUT.ENABLE(1000000); DBMS_OUTPUT.PUT_LINE('Message from the cleaning lady: I''m starting the process now'); OPEN tab_cur; FETCH tab_cur BULK COLLECT INTO tablist; CLOSE tab_cur; FOR i in 1 .. tablist.COUNT LOOP clean_del(tablist(i)); END LOOP; DBMS_OUTPUT.PUT_LINE('Message from the cleaning lady: I''m finished now. Have a nice day.'); END; /