Customized tablespace retention policies and customized retention policies

Customized tablespace retention policies and customized retention policies

In large databases, the recycle bin is large, and there is a lot of garbage. It is very slow to query tablespaces. To prevent accidental operations, it is impossible to add perge to the drop table every time. At this time, there is more and more garbage, and the table space will take a long time. At this time, it is necessary to customize a tablespace retention policy. Share my scripts and add them to the job. Create or replace procedure lhj_delete_recyclebin (preserve_date in number) is/* stored procedure name: lhj_delete_recyclebin prepared by: XXX function: Clear the recycle bin (7 days by default) purge table xx drops the index purge of the table. Usage: begin lhj_delete_recyclebin (6); end; */v_ SQL varchar2 (1000); cursor c (v_preserve_date number) isselect owner, original_name, droptime, type from dba_recyclebin where/* owner = 'ggs 'and */type = 'table' and to_date (droptime, 'yyyy-mm-dd hh24: mi: ss ') <trunc (sysdate)-v_preserve_date order by 3; -- keep the table for one week to modify the 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 then rollback; end loop; end;