When poking around a system, make sure to look at the dba_recyclebin. Oftentimes, this functionality within Oracle seems to be overlooked and forgotten about. This is due to the fact that the default for the parameter “recyclebin” in Oracle 19c is “on”. You can also manage this parameter at the session level as well.

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/RECYCLEBIN.html#GUID-B6F1AA5B-DF1E-4EAF-BDFD-E70EFBAD997A

Should a table drop occur by mistake, often the table can be recovered so long as the column “CAN_UNDROP” for that object shows the value “YES” in the dba_recyclebin table by issuing the command:

select owner, object_name, original_name, can_undrop from dba_recyclebin;
flashback table hr.emp to before drop;

It’s good functionality. Also the recyclebin helps in the case where your application must drop / create / recreate objects on a frequent basis because the operation is much faster as the drop command does not have to wait for the segments to drop.

The area that most forget about is “purging” the recyclebin. As a matter of fact, I recently saw a system the other day that had 5M objects in the recyclebin totaling several hundred GB of space most of which was several years old. Thats several hundred GB of space being backed up every day for objects which have been dropped!

Generally, purging the recyclebin is a manual operation. However, there is a way to implement a dbms_scheduler job which will do this on a regularly scheduled basis.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
	job_name => 'WEEKLY_PURGE_RECYCLEBIN',
	job_type => 'PLSQL_BLOCK',
	job_action => 'DECLARE
		V_PRE_RECYCLEBIN_COUNT PLS_INTEGER := 0;
		V_PRE_RECYCLEBIN_SIZE NUMBER(38) :=0;
		V_POST_RECYCLEBIN_COUNT PLS_INTEGER := 0;
        V_SQL_STATEMENT VARCHAR2(500);
        V_DAYS_TO_PURGE PLS_INTEGER := 7;
        V_STALE_DICT_STATS PLS_INTEGER := 0;
        err_code NUMBER;
        err_msg VARCHAR2(500);
        CURSOR purge_bin_cursor IS select owner, object_name, original_name from dba_recyclebin where type=''TABLE'' and to_date(droptime,''YYYY-MM-DD:HH24:MI:SS'') < sysdate - V_DAYS_TO_PURGE;
		BEGIN
			select round(nvl(sum(space * 8192),0)/1024/1024,2) into V_PRE_RECYCLEBIN_SIZE from dba_recyclebin;
			select count(*) into V_PRE_RECYCLEBIN_COUNT from dba_recyclebin where to_date(droptime,''YYYY-MM-DD:HH24:MI:SS'')  0
			THEN
				DBMS_OUTPUT.PUT_LINE(''Purging ''||V_PRE_RECYCLEBIN_COUNT||'' objects, reclaiming ''||V_PRE_RECYCLEBIN_SIZE||'' MB from the recyclebin'');
				FOR purge_rec in purge_bin_cursor
				LOOP
                	V_SQL_STATEMENT := ''purge table ''||purge_rec.owner||''."''||purge_rec.object_name||''"'';
                	DBMS_OUTPUT.PUT_LINE(''Executing: ''||V_SQL_STATEMENT||'' for original object name ''||purge_rec.original_name);
					EXECUTE IMMEDIATE V_SQL_STATEMENT;
				END LOOP;
				select count(*) into V_POST_RECYCLEBIN_COUNT from dba_recyclebin;
				DBMS_OUTPUT.PUT_LINE(''Recyclebin has ''||V_POST_RECYCLEBIN_COUNT||'' objects remaining in the recyclebin'');
				select count(*) into V_STALE_DICT_STATS from dba_tab_statistics where owner = ''SYS'' AND TABLE_NAME = ''OBJ$'' and stale_stats = ''YES'';
				IF V_STALE_DICT_STATS = 1
				THEN
					DBMS_OUTPUT.PUT_LINE(''Executing Dictionary Statistics'');
					DBMS_STATS.GATHER_DICTIONARY_STATS;
				ELSE
					DBMS_OUTPUT.PUT_LINE(''Dictionary Statistics not stale'');
				END IF;
			ELSE
				DBMS_OUTPUT.PUT_LINE(''Nothing to purge from the recyclebin'');
			END IF;
		EXCEPTION
			WHEN OTHERS THEN
		    err_code := SQLCODE;
      		err_msg := SUBSTR(SQLERRM, 1, 500);
      		DBMS_OUTPUT.PUT_LINE(''An error was encountered - ''||SQLCODE||'' -ERROR- ''||SQLERRM);
		END;',
	start_date => sysdate,
	repeat_interval => 'FREQ=MONTHLY;BYDAY=1SAT;BYHOUR=18',
	end_date => NULL,
	enabled => TRUE,
	auto_drop => FALSE,
	comments => 'This job purges the recyclebin on the first Saturday of the Month at 18:00'
);
END;
/

As implemented above, this job will run once per week and delete everything that is older than 7 days that is in the recyclebin. No longer will you have to worry about the recyclebin and it containing too many un-purged objects!

Enjoy!