oracle数据库日期,时间使用,删除恢复等.docx

Oracle批量删除表、索引等对象、批量恢复Drop操作删除的表、索引等对象虽然能够恢复表结构和数据，但是索引，主键，约束等，并不能完全恢复。/**********************************************************************查询Drop操作删除的对象select * from recyclebin ;单个对象（表、索引或是通过drop操作删除的其它对象）恢复flashback table（被删除的对象类型）被删除的对象名称 to before drop***********************************************************************/--Demo单张表恢复flashback table Table_XXX to before drop;-- 创建批量恢复表的存储过程create or replace procedure RecoveryOfTable isbegin declare /*********************************************************************** select flashback table ||a.original_name|| to before drop from recyclebin a where a.operation = DROP and a.type=TABLE恢复其它类型对象请将flashback table 这里换为其它类型， type=相应的类型可加更多条件限制，具体查询recyclebin***********************************************************************/ cursor cur_flashback is select flashback table ||a.original_name|| to before drop from recyclebin a where a.operation = DROP and a.type=TABLE; v_name varchar2(4000);begin open cur_flashback; fetch cur_flashback into v_name; while cur_flashback%found loop execute immediate v_name; fetch cur_flashback into v_name; end loop; dbms_output.put_line(恢复成功!); close cur_flashback;end; end;--执行存储过程（使用plsql操作时）beginRecoveryOfTable;end;--执行存储过程（使用SQL调用）execute RecoveryOfTable;/**************************************批量删除数据库表 select * from user_tables where table_name like Table_XXX%;存储过程体**************************************/declare cursor cur_delete is select drop table || table_name from user_tables where table_name like Table_XXX%;/*************************************可模板条件删除其它类型对象如：索引等**************************************/ 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(将所有Table_XXX开头的表删除!); close cur_delete;end;执行PURGE TABLE ZYM_USER.BIN$UagqFGZsZcvgQAoKpSB9uQ==$0--