Recycle Bin

DROP TABLE: the TABLE and all dependent INDEXes, TRIGGERs, CONSTRAINTs, LOBs, TABLE PARTITIONs, INDEX PARTITIONs go into the recyclebin.DROP USER: objects owned by that User are dropped and DO NOT go into the recyclebin.DROP CLUSTER: member tables DO NOT go into the recycle bin.DROP TYPE: dependent objects (subtypes) DO NOT go into the recycle bin.DROP INDEX, the index DOES NOT go into the recycle bin. Indexes only go into the recycle bin if the dependent table is dropped.

Check

SHOW PARAMETER recyclebin;

SELECT COUNT(*) FROM sys.RECYCLEBIN$;

To give a breakdown of the contents of the recyclebin...

COLUMN type# NOPRINT

SELECT type#, decode(type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',

4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',

7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',

11, 'PACKAGE BODY', 12, 'TRIGGER',

13, 'TYPE', 14, 'TYPE BODY',

19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',

22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',

28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',

32, 'INDEXTYPE', 33, 'OPERATOR',

34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',

40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',

42, 'MATERIALIZED_VIEW/REWRITE EQUIVALENCE/MATERIALIZED ZONEMAP',

43, 'DIMENSION',

44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',

48, 'CONSUMER GROUP',

55, 'XML SCHEMA', 56, 'JAVA DATA',

57, 'EDITION', 59, 'RULE',

60, 'CAPTURE', 61, 'APPLY',

62, 'EVALUATION CONTEXT',

66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',

72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 79, 'CHAIN',

81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',

90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',

94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',

100, 'FILE WATCHER', 101, 'DESTINATION',

114, 'SQL TRANSLATION PROFILE',

115, 'UNIFIED AUDIT POLICY',

144, 'MINING MODEL PARTITION',

148, 'LOCKDOWN PROFILE',

150, 'HIERARCHY',

151, 'ATTRIBUTE DIMENSION',

152, 'ANALYTIC VIEW',

'UNDEFINED') TYP,

COUNT(*)

FROM sys.RECYCLEBIN$ o

GROUP BY type#

/

COLUMN type# PRINT

Enable

ALTER SYSTEM SET recyclebin = ON SCOPE=both;

Disable

ALTER SYSTEM SET recyclebin = OFF SCOPE=both;

Purge

To avoid the recyclebin when dropping an object you can use...

DROP TABLE &table_name PURGE;

To delete specific objects from the recyclebin use...

PURGE TABLE &table_name;

PURGE INDEX &index_name;

To purge all objects from a single tablespace for the user you are logged in as...

PURGE TABLESPACE &ts_name;

To purge all objects from the recyclebin of the user you are logged in as...

PURGE RECYCLEBIN

To purge all objects from the recyclebin for all users...

PURGE DBA_RECYCLEBIN;

You need the PURGE DBA_RECYCLEBIN privilege (or SYSDBA prior to 12c).
NOTE: In the unlikely event that rows remain in the recyclebin after PURGE DBA_RECYCLEBIN do this...PURGE DBA_RECYCLEBIN;SELECT COUNT(*) FROM sys.RECYCLEBIN$;TRUNCATE TABLE sys.RECYCLEBIN$;SELECT COUNT(*) FROM sys.RECYCLEBIN$;

Flashback

FLASHBACK TABLE &table_name TO BEFORE DROP;

You can use the actual table_name or the BIN$ name from the recycle bin.

FLASHBACK TABLE &table_name TO BEFORE DROP RENAME TO &new_table_name;