What is the Recycle bin and How to Restore Dropped Tables from Recycle Bin

Hi,

I will explain What is the Recycle bin and How to Restore Dropped Tables from Recycle Bin in this post.

 

You can read the Flash back articles from the following link if you want to learn more detail about Flash back.

Flashback Database, Table, Query Tutorials For Beginners in the Oracle Database

 

Oracle database has the recycle bin which is a data dictionary table and contains information about dropped objects. Until you don’t use purge option, all dropped tables and its objects such as indexes, constraints and etc are not removed and still occupy space.

Actually the recycle bin is any life buoy for the database administrators, because sometime we can restore or flash back the dropped tables from recycle bin, if these objects are still in the recycle bin.

 

You can check if the recycle bin is on or off as follows.

SQL> show parameter recyclebin;

NAME                   TYPE                VALUE
------------------------------------ ----------- 
recyclebin             string                on

 

To disable the recycle bin, you can disable the recycle bin both for session and system level as follows.

ALTER SESSION SET recyclebin = OFF;

ALTER SYSTEM SET recyclebin = OFF;

You can enable the recycle bin both for session and system level as follows.

 

ALTER SESSION SET recyclebin = ON;

ALTER SYSTEM SET recyclebin = ON;

Now, lets go to make an example and recover a dropped table from recycle bin as follows.

SQL> show parameter recyclebin

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on

 

Following table has 53 rows.

SQL> select count(*) from msdeveci.test;

COUNT(*)
----------
53

 

Lets drop it.

SQL> drop table msdeveci.test;

Table dropped.

 

Check if it exists.

SQL> select * from msdeveci.test;
select * from msdeveci.test
*
ERROR at line 1:
ORA-00942: table or view does not exist

 

Now lets go to flashback table from recycle bin as follows.

SQL> flashback table msdeveci.test to before drop;

Flashback complete.

 

Check if table exists or not.

SQL> select count(*) from msdeveci.test;

COUNT(*)
----------
53

 

You can list the objects and droptime from recycle bin as follows.

 

SQL> select object_name, droptime from dba_recyclebin;

OBJECT_NAME DROPTIME
------------------------------ -------------------
BIN$4YkbXtBoAdngQwEAAH8n+g==$0 2020-03-30:10:49:57
BIN$4YkbXtBpAdngQwEAAH8n+g==$0 2020-03-30:10:49:57
BIN$4YkbXtBqAdngQwEAAH8n+g==$0 2020-03-30:10:49:57
BIN$4YkbXtBrAdngQwEAAH8n+g==$0 2020-03-30:10:49:57
BIN$4YkbXtBsAdngQwEAAH8n+g==$0 2020-03-30:10:49:57
BIN$4YkcLLzGAd3gQwEAAH/TdQ==$0 2020-03-30:10:50:11
BIN$4YkcLLzHAd3gQwEAAH/TdQ==$0 2020-03-30:10:50:11
BIN$4YkcttMiAd/gQwEAAH8VjQ==$0 2020-03-30:10:50:20
BIN$4YkcttMjAd/gQwEAAH8VjQ==$0 2020-03-30:10:50:20
BIN$4YkdYIHCAeHgQwEAAH87MA==$0 2020-03-30:10:50:31
BIN$4YkdYIHDAeHgQwEAAH87MA==$0 2020-03-30:10:50:31


RECYCLEBIN

You can purge recycle bin as follows. purge recyclebin will remove all objects from the user’s recycle bin and release all space associated with objects in the recycle bin

SQL> purge recyclebin;

Recyclebin purged.

 

Purge DBA_RECYCLEBIN

Purge dba_recyclebin needs sysdba privilige and it removes all objects from the system-wide recycle bin, and is equivalent to purging the recycle bin of every user

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL>

 

Tablespace Purge

purge tablespace option purges all the objects residing in the specified tablespace from the recycle bin.

SQL> purge tablespace Users;

Tablespace purged.

SQL>



You can purge any table as follows.

SQL> PURGE TABLE MSDEVECI.TEST;
Table purged.


You can purge any dropped table as follows.

SQL> purge table "BIN$cxP2b6/dEnrgVQBAAAAADQ==$1";

Table purged.

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/

 552 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Check Also

Alter System Flush Buffer Cache in Oracle

Hi, I will explain Alter System Flush Buffer Cache in Oracle in this post.  4,017 views …

Leave a Reply