DBA_RECYCLEBIN purge指定日期前的表

SummaryHow to purge DBA_RECYCLBIN for objects older than x days/minutes? or do we have RECYCLEBIN RETENTION feature or truncate recyclebin ?--------------------------------------------------------------------------------------DBA_RECYCLEBIN has a column called DROPTIME.But we can not use the below queries to purge or delete the recyclebin objects older than 7 daysDELETE from DBA_RECYCLEBIN where droptimesysdate-7; (wrong do not use)PURGE DBA_RECYCLEBIN where droptimesysdate-7; (wrong do not use)SQL DELETE from DBA_RECYCLEBIN where droptimesysdate-7;DELETE from DBA_RECYCLEBIN where droptimesysdate-7*ERROR at line 1:ORA-01752: cannot delete from view without exactly one key-preserved tableSQL PURGE DBA_RECYCLEBIN where droptimesysdate-7;PURGE DBA_RECYCLEBIN where droptimesysdate-7*ERROR at line 1:ORA-00933: SQL command not properly endedSolutionThere is no direct command or RECYCLEBIN_RETENTION parameter to purge/delete with the droptime clause, so we have to use the workaround of identifying the objects and then drop them manually. There is an enhancement request filed through an Unpublished Bug 6694377, so the feature is still not available, but there is an easy workaround available.Unpublished Bug 6694377 : ENHANCE PURGE COMMAND WITH TIME SPECIFIC CLAUSESELECT .... FROM DBA_RECYCLEBIN can have WHERE condition,PURGE DBA_RECYCLEBIN can not have WHERE condition and throws syntax errors like ORA-00933: SQL command not properly endedSo you can workaround this by dynamically preparing sql queries to purge the recycle bin objectsExamples:1. Purge tables dropped before 7 days (older than 7 days)SQL spool purge_table_older_than_7_days.sqlSQL select purge table ||owner||.||OBJECT_NAME||;from dba_recyclebinwhere typeTABLE and to_date(droptime,YYYY-MM-DD:HH24:MI:SS)sysdate-7;SQL spool off;1. Purge tables dropped before 5 minutes (older than 5 minutes), use sysdate-(5/(24*60)) because 24 hours per day, 60 minutes per hourSQL spool purge_table_older_than_5_minutes.sqlSQL select purge table ||owner||.||OBJECT_NAME||;from dba_recyclebinwhere typeTABLE and to_date(droptime,YYYY-MM-DD:HH24:MI:SS)sysdate-(5/(24*60));SQL spool off;SQL spool purge_table_older_than_5_min.txtSQL purge_table_older_than_5_minutes.sqlSQL spool off;