How To Recover Dropped Tables with Oracle Recyclebin

 

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.

 

Query Dropped Objects:

We can query dropped objects from RECYCLEBIN, USER_RECYCLEBIN and DBA_RECYCLEBIN.

DBA_RECYCLEBIN requires dba authorization.

 

Create and drop a sample table and index.

 

When you query the recyclebin again, you can see the table and index.

 

If you create a new table with the same name and drop, it is kept in the recyclebin with a version.

 

To see that the two tables are different, we made an insert into one. We can see the difference with the following query.

 

Recover Dropped Tables:

We use the flashback command to recover dropped the tables in the recyclebin.

 

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.

 

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.

 

Purge the entire recycle bin (required to connect with sys);

 

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *