In large databases, the Recycle Bin is very large, garbage a lot, table space is particularly slow to query. In order to prevent accidental operation, it is not possible to add perge each time the drop table, when the garbage more and more, look up the table space to wait a long time. At this point, it is necessary to customize a table space retention policy. Share my script below and automatically clean up when you join the job. Create or Replace procedure Lhj_delete_recyclebin (preserve_date in number) is/* stored procedure name: Lhj_delete_ RecycleBin Writer: xxx function: Clean the Recycle Bin (default reserved for 7 days) purge table XX will purge the index corresponding to its usage: Begin Lhj_delete_recyclebin (6); End;*/v_sql varchar2 (+), cursor C (v_preserve_date number) Isselect owner, Original_name, Droptime, type from DB A_recyclebin where/*owner= ' GGS ' and * * type = ' TABLE ' and to_date (Droptime, ' Yyyy-mm-dd hh24:mi:ss ') < Trunc (SYSDA TE)-V_preserve_date order by 3; --Keep a week's table modifiable time v_preserve_date Number: = Preserve_date; begin if v_preserve_date is null then v_preserve_date: = 7; End If; For x in C (v_preserve_date) loop begin v_sql: = ' purge table ' | | X.owner | | '.' || X.original_name; --dbms_output.put_line (V_sql); Execute immediate v_sql; Exception when others and then rollback; End; end loop; end;
Customizing table space Retention Policies