<\/p>\n
In this article I will tell you about Recyclebin which is one of the beautiful features announced with Oracle 10g version.<\/p>\n
With the Recyclebin feature, we can recover tables that were dropped without purge.<\/p>\n
With this feature we can recover many incorrectly dropped tables.<\/p>\n
<\/p>\n
If the recyclebin parameter is off, it is necessary to set it as on and restart it.<\/p>\n
SQL> show parameter recyclebin\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ --------------------------------- ------------------------------\r\nrecyclebin string off\r\n\r\n\r\nSQL> alter system set recyclebin=on scope=spfile sid='*';\r\n\r\nSystem altered.<\/pre>\n<\/p>\n
Query Dropped Objects:<\/h3>\n
We can query dropped objects from RECYCLEBIN, USER_RECYCLEBIN and DBA_RECYCLEBIN.<\/p>\n
DBA_RECYCLEBIN requires dba authorization.<\/p>\n
SQL> SELECT OBJECT_NAME,ORIGINAL_NAME,TYPE FROM RECYCLEBIN;\r\n\r\nno rows selected<\/pre>\n<\/p>\n
Create and drop a sample table and index.<\/p>\n
SQL> create table A(id number);\r\n\r\nTable created.\r\n\r\nSQL> create index AA on A(id);\r\n\r\nIndex created.\r\n\r\nSQL> insert into A values(1);\r\n\r\n1 row created.\r\n\r\nSQL> select * from A;\r\n\r\nID\r\n----------\r\n1\r\n\r\nSQL> drop table A;\r\n\r\nTable dropped.<\/pre>\n<\/p>\n
When you query the recyclebin again, you can see the table and index.<\/p>\n
SQL> SELECT OBJECT_NAME,ORIGINAL_NAME,TYPE FROM RECYCLEBIN;\r\n\r\nOBJECT_NAME ORIGINAL_NAME TYPE\r\n-------------------------------- -------------------------------- --------------------------------\r\nBIN$Pkg9OFV8AtzgU6wSxRdguw==$0 A TABLE\r\nBIN$Pkg9OFV7AtzgU6wSxRdguw==$0 AA INDEX<\/pre>\n<\/p>\n
If you create a new table with the same name and drop, it is kept in the recyclebin with a version.<\/p>\n
SQL> SELECT OBJECT_NAME,ORIGINAL_NAME,TYPE FROM RECYCLEBIN;\r\n\r\nOBJECT_NAME ORIGINAL_NAME TYPE\r\n-------------------------------- -------------------------------- --------------------------------\r\nBIN$Pkg9OFV8AtzgU6wSxRdguw==$0 A TABLE\r\nBIN$Pkg9OFV8AtzgU6wSxRdguw==$0 A TABLE\r\nBIN$Pkg9OFV7AtzgU6wSxRdguw==$0 AA INDEX<\/pre>\n<\/p>\n
To see that the two tables are different, we made an insert into one. We can see the difference with the following query.<\/p>\n
SQL> select * from \"BIN$Pkg9OFV9AtzgU6wSxRdguw==$0\";\r\n\r\nno rows selected\r\n\r\nSQL> select * from \"BIN$Pkg9OFV8AtzgU6wSxRdguw==$0\";\r\n\r\nID\r\n----------\r\n1<\/pre>\n<\/p>\n
Recover Dropped Tables:<\/h3>\n
We use the flashback command to recover dropped the tables in the recyclebin.<\/p>\n
SQL> flashback table A to before drop;\r\n\r\nFlashback complete.\r\n\r\nSQL> select * from A;\r\n\r\nno rows selected<\/pre>\n<\/p>\n
There were two tables named A in Recyclebin. When we want to recover with flashback, it will recover the most recently dropped table.<\/p>\n
If we want to recover the other table, we should flashback with the name on the recyclebin.<\/p>\n
SQL> flashback table \"BIN$Pkg9OFV8AtzgU6wSxRdguw==$0\" to before drop;\r\n\r\nFlashback complete.\r\n\r\nSQL> select * from A;\r\n\r\nID\r\n----------\r\n1<\/pre>\n<\/p>\n
Restrictions on the use of recyclebin:<\/h3>\n
\n
- 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.<\/li>\n
- 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.<\/li>\n
- When a table is recovered from recyclebin, referential integrity constraints that point to other tables in the table are lost.<\/li>\n<\/ul>\n
<\/p>\n
Purge Recyclebin:<\/h3>\n
You can purge your own recyclebin using the command below.<\/p>\n
SQL> PURGE RECYCLEBIN;\r\n\r\nRecyclebin purged.<\/pre>\n<\/p>\n
Purge the entire recycle bin (required to connect with sys);<\/h3>\n
-bash-4.3$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 7 17:58:46 2016\r\n\r\nCopyright (c) 1982, 2013, Oracle. All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production\r\nWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,\r\nData Mining and Real Application Testing options\r\n\r\nSQL> PURGE DBA_RECYCLEBIN;\r\n\r\nDBA Recyclebin purged.<\/pre>\n<\/p>\n
Disable Recyclebin:<\/h3>\n
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.<\/p>\n
SQL> alter session set recyclebin=off ;\r\n\r\nSession altered.\r\n\r\n\r\nSQL> alter system set recyclebin=off scope=spfile sid='*'\r\n\r\n\r\nSession altered.<\/pre>\n\n<\/div>\n