Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In this article I will tell you about Recyclebin which is one of the beautiful features announced with Oracle 10g version.
With the Recyclebin feature, we can recover tables that were dropped without purge.
With this feature we can recover many incorrectly dropped tables.
Activate Recyclebin:
If the recyclebin parameter is off, it is necessary to set it as on and restart it.
1 2 3 4 5 6 7 8 9 10 | SQL> show parameter recyclebin NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ recyclebin string off SQL> alter system set recyclebin=on scope=spfile sid='*'; System altered. |
Query Dropped Objects:
We can query dropped objects from RECYCLEBIN, USER_RECYCLEBIN and DBA_RECYCLEBIN.
DBA_RECYCLEBIN requires dba authorization.
1 2 3 | SQL> SELECT OBJECT_NAME,ORIGINAL_NAME,TYPE FROM RECYCLEBIN; no rows selected |
Create and drop a sample table and index.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> create table A(id number); Table created. SQL> create index AA on A(id); Index created. SQL> insert into A values(1); 1 row created. SQL> select * from A; ID ---------- 1 SQL> drop table A; Table dropped. |
When you query the recyclebin again, you can see the table and index.
1 2 3 4 5 6 | SQL> SELECT OBJECT_NAME,ORIGINAL_NAME,TYPE FROM RECYCLEBIN; OBJECT_NAME ORIGINAL_NAME TYPE -------------------------------- -------------------------------- -------------------------------- BIN$Pkg9OFV8AtzgU6wSxRdguw==$0 A TABLE BIN$Pkg9OFV7AtzgU6wSxRdguw==$0 AA INDEX |
If you create a new table with the same name and drop, it is kept in the recyclebin with a version.
1 2 3 4 5 6 7 | SQL> SELECT OBJECT_NAME,ORIGINAL_NAME,TYPE FROM RECYCLEBIN; OBJECT_NAME ORIGINAL_NAME TYPE -------------------------------- -------------------------------- -------------------------------- BIN$Pkg9OFV8AtzgU6wSxRdguw==$0 A TABLE BIN$Pkg9OFV8AtzgU6wSxRdguw==$0 A TABLE BIN$Pkg9OFV7AtzgU6wSxRdguw==$0 AA INDEX |
To see that the two tables are different, we made an insert into one. We can see the difference with the following query.
1 2 3 4 5 6 7 8 9 | SQL> select * from "BIN$Pkg9OFV9AtzgU6wSxRdguw==$0"; no rows selected SQL> select * from "BIN$Pkg9OFV8AtzgU6wSxRdguw==$0"; ID ---------- 1 |
Recover Dropped Tables:
We use the flashback command to recover dropped the tables in the recyclebin.
1 2 3 4 5 6 7 | SQL> flashback table A to before drop; Flashback complete. SQL> select * from A; no rows selected |
There were two tables named A in Recyclebin. When we want to recover with flashback, it will recover the most recently dropped table.
If we want to recover the other table, we should flashback with the name on the recyclebin.
1 2 3 4 5 6 7 8 9 | SQL> flashback table "BIN$Pkg9OFV8AtzgU6wSxRdguw==$0" to before drop; Flashback complete. SQL> select * from A; ID ---------- 1 |
Restrictions on the use of recyclebin:
- When tables with bitmap join indexes are dropped, these indexes are not stored in recyclebin and these indexes cannot be recovered when the table is recovered from recyclebin.
- The same applies to materialized view logs. When a table is dropped, all the materialized view logs defined on this table are permanently deleted, not stored in recyclebin.
- When a table is recovered from recyclebin, referential integrity constraints that point to other tables in the table are lost.
Purge Recyclebin:
You can purge your own recyclebin using the command below.
1 2 3 | SQL> PURGE RECYCLEBIN; Recyclebin purged. |
Purge the entire recycle bin (required to connect with sys);
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | -bash-4.3$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 7 17:58:46 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> PURGE DBA_RECYCLEBIN; DBA Recyclebin purged. |
Disable Recyclebin:
We can disable the Recyclebin feature at the session-based or database level. To disable at the database level, you need to restart when the recyclebin parameter is changed.
1 2 3 4 5 6 7 8 9 | SQL> alter session set recyclebin=off ; Session altered. SQL> alter system set recyclebin=off scope=spfile sid='*' Session altered. |
