Enable the database recyclebin
The recycle bin is enabled by default.
SQL> ALTER SYSTEM SET recyclebin = ON;
To disable for the entire database (not recommended):
SQL> ALTER SYSTEM SET recyclebin = OFF;
To enable the recycle bin for a session:
SQL> ALTER SESSION SET recyclebin = ON;
To disable the recycle bin for a session:
SQL> ALTER SESSION SET recyclebin = OFF;
Show recyclebin contents
To see the objects in the recyclebin:
SHOW RECYCLEBIN
example :
SQL> Spool@C:\spool.txt
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
A1 BIN$siqFlPW1SFuXLPVLJjrANw==$0 TABLE 2014-04-03:19:09:37
ACCTS BIN$D6clCGF4RRSyb44F3bq3Hw==$0 TABLE 2014-04-11:19:11:46
B1 BIN$s/+UAMluRJajm/4Q8Vwhqg==$0 TABLE 2014-04-03:19:14:29
BACKUP_USER BIN$E8yLUMdjSbyIdFFSWQbpIg==$0 TABLE 2014-07-02:16:51:43
BACKUP_USER BIN$AHSi9Ct1QC+GHk2bgX6cxA==$0 TABLE 2014-07-02:16:50:29
EMP1 BIN$56gA2jGBQ9yyCJcNmEtWcw==$0 TABLE 2014-04-08:02:13:54
EMP2 BIN$8rVdVYpGT1CdrYiGVka1qA==$0 TABLE 2014-04-08:02:14:16
T2 BIN$cchC6vqgTwy74skkDYLDSw==$0 TABLE 2014-04-08:02:14:56
T3 BIN$2gPttPfMSL6e72lC6GNXfg==$0 TABLE 2014-04-08:02:14:59
TEMPEMP BIN$+cYkaoUFRSqwLF3SxLviHg==$0 TABLE 2014-06-06:19:56:33
TEMPEMP1 BIN$wmz6BXHMQhq3fy+YgiWHbg==$0 TABLE 2014-06-06:19:56:25
TEMPEMP2 BIN$K/ZJMZepTTqhW/LRXWXzrg==$0 TABLE 2014-06-06:19:56:17
USERS BIN$XdXcRPU1QpSbCp2kETLqHA==$0 TABLE 2014-07-02:16:43:38
USERS BIN$NzZzw7e3SxW+gv57eF+oOA==$0 TABLE 2014-07-02:16:43:27
USERS BIN$B/LKWlYuT3C8GjA9Jdqg1Q==$0 TABLE 2014-07-02:16:42:20
USERS BIN$OfbacBcHRK+e4C6graZmxg==$0 TABLE 2014-07-02:16:41:48
USERS BIN$C+4IndfvRb24zhGd8z/W0g==$0 TABLE 2014-07-02:16:41:24
USERS BIN$4Lov4lqiRuO+o4yJvLqX+A==$0 TABLE 2014-07-02:16:40:59
USERS BIN$P2vOQ7E6Qcm5u2NLsHZx4g==$0 TABLE 2014-07-02:16:38:25
SQL> Spool Out;
Clear recyclebin
To remove all dropped objects from the recyclebin (current user):
PURGE RECYCLEBIN;
SQL> PURGE users_RECYCLEBIN;
PURGE users_RECYCLEBIN
*
ERROR at line 1:
ORA-38302: invalid PURGE option
SQL> PURGE RECYCLEBIN;
Recyclebin purged.
SQL> show recyclebin;
SQL> PURGE RECYCLEBIN;
Recyclebin purged.
SQL> SHOW RECYCLEBIN;
SQL>
To remove all dropped objects from the recyclebin (system wide):
PURGE DBA_RECYCLEBIN;
Tables can also be droped without sending them to the recyclebin. Example:
DROP TABLE t1 PURGE;
Examples
Drop a table:
SQL> DROP TABLE t1;
Undrop the table:
SQL> FLASHBACK TABLE t1 TO BEFORE DROP;
0 Comments