Oracle Flashback Drop

Oracle Requirements:

Recycle Bin must be enabled (RECYCLEBIN=ON).

DROP TABLE without PURGE.

Licensing:

Available in all Oracle Database editions.

Flashback Drop in Oracle

The Flashback Drop feature in Oracle allows you to restore tables that were accidentally dropped, as long as the recycle bin is enabled. This feature provides an additional layer of protection against accidental deletions, minimizing data loss and recovery time.

How Does Flashback Drop Work?

When a table is dropped in Oracle, it is not immediately removed. Instead, the object is moved to the recycle bin, where it remains available for recovery until the space is needed for other objects.

Enabling the Recycle Bin

The recycle bin can be enabled or disabled at both the session and system levels.

Session Level


SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SESSION SET recyclebin = ON;

System Level


SQL> ALTER SYSTEM SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = ON;

Listing Objects in the Recycle Bin

**SQL*Plus Command**


SQL> SHOW RECYCLEBIN;


SQL> SELECT * FROM DBA_RECYCLEBIN;

Be aware that objects in the SYSTEM tablespace will not go to the recyclebin.

  
SQL> sho user
USER is "SYS"
SQL> sho parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
SQL> show recyclebin;
SQL> set pages 200 lin 200
SQL> create table MY_TABLE as select * from dba_objects;

Table created.

SQL> select count(*) from MY_TABLE;

  COUNT(*)
----------
     73176

SQL> drop table MY_TABLE;

Table dropped.

SQL> show recyclebin;

SQL> create table MY_TABLE tablespace USERS as select * from dba_objects ;

Table created.

SQL> drop table MY_TABLE;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
MY_TABLE         BIN$KgaCqB/NC7fgY6dEqMCDxw==$0 TABLE        2024-12-24:12:10:15
SQL>

If you want to query some data from the recyclebin, the table name will be displayed in the RECYCLEBIN_NAME from the query above:


SQL> SELECT * FROM "BIN$KgaCqB/NC7fgY6dEqMCDxw==$0";

Restoring a Table from the Recycle Bin

Restore with the Original Name


SQL> FLASHBACK TABLE MY_TABLE TO BEFORE DROP;

You can also use CTAS to bring data from the Recycle Bin

Restore with the name you want


SQL> CREATE TABLE NEW_TABLE_NAME as SELECT * FROM "BIN$KgaCqB/NC7fgY6dEqMCDxw==$0";

Purging the Recycle Bin

And in case you want to erase your recycle bin, here are the main options:

Command	Description
PURGE TABLE tablename;	Specific table.
PURGE INDEX indexname;	Specific index.
PURGE TABLESPACE ts_name;	All tables in a specific tablespace.
PURGE TABLESPACE ts_name USER username;	All tables in a specific tablespace for a specific user.
PURGE RECYCLEBIN;	The current user's entire recycle bin.
PURGE DBA_RECYCLEBIN;	The whole recycle bin (all users).

Search This Blog

BHThoughts