ORA-38307: object not in RECYCLE BIN

When I was trying to purge object under recycle bin I got following error.

SQL> purge TABLE BIN$TBffE+37D77gRAgAIPeSWg==$0;
purge TABLE BIN$TBffE+37D77gRAgAIPeSWg==$0
*
ERROR at line 1:
ORA-00933: SQL command not properly ended


So I used Double quotation to resolved the issue. But still I was getting another error like below.

SQL> purge TABLE "BIN$Y+tPc7dSNrzgRAgAIPeSWg==$0";
purge TABLE "BIN$Y+tPc7dSNrzgRAgAIPeSWg==$0"
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN

To avoid this error I had to put owner.object_name.

SQL> purge TABLE scott."BIN$Y+tPc7dSNrzgRAgAIPeSWg==$0";

Table purged.


Finally I have created below procedure so that I can schedule it to run with given parameter. Parameter may be like purge objects older then 30 days.


create or replace procedure cln_recylebin( p_day in number)
is
begin
for i in (SELECT owner,object_name,type FROM dba_RECYCLEBIN where can_purge='YES'
and to_date(droptime,'yyyy-mm-dd:HH24:MI:SS') < sysdate-p_day) loop
dbms_output.put_line(i.type||' "'||i.object_name||'"');
execute immediate 'purge '||i.type||' '||i.owner||'."'||i.object_name||'"';
end loop;
end;
/


Now just call the procedure like:

SQL> exec cln_recylebin(30);