Oracle batch Delete objects such as tables and indexes, batch restore objects such as tables and indexes deleted by the Drop operation

/*************************************** * ****************************** Query the select * from object deleted by the Drop operation recyclebin; restore a flashback table (the type of the deleted object) for a single object (table, index, or other objects deleted through the drop Operation) name of the deleted object to before drop ******************************** ***************************************/ -- Demo restoring flashback table Table_XXX to before drop in a single table; -- create or replace procedure RecoveryOfTable isbegin declare /************* **************************************** * ***************** Select 'flashback table' |. original_name | 'to before drop' from recyclebin a where. operation = 'drop' and. type = 'table' to restore other types of objects, replace flashback TABLE with other types. type = 'corresponding type' can add more conditions, for details, see recyclebin ************************************* * ********************************/cursor cur_flashback is select' flashback table '|. original_name |'t O before drop 'from recyclebin a where. operation = 'drop' and. type = 'table'; v_name varchar2 (4000); begin open cur_flashback; fetch cur_flashback into v_name; while deleting % found loop execute immediate v_name; fetch cur_flashback into v_name; end loop; dbms_output.put_line ('recovery successful! '); Close cur_flashback; end; -- execute the Stored Procedure (when plsql is used) beginRecoveryOfTable; end; -- execute the Stored Procedure (called using SQL) execute RecoveryOfTable; /************************************ batch delete database Table select * from user_tables where table_name like 'table _ XXX % '; stored Procedure body ************************************* */declare cursor cur_delete is select 'drop table' | table_name from user_tables where table_name like 'table _ XXX % '; /*********** * ************************* Other types of objects can be deleted by template conditions, such: ************************************** /v_name varchar2 (4000 ); begin open cur_delete; fetch cur_delete into v_name; while cur_delete % found loop execute immediate v_name; fetch cur_delete into v_name; end loop; dbms_output.put_line ('delete all tables starting with Table_XXX! '); Close cur_delete; end;