How to restore a dropped table from recycle bin in Oracle

Leave a reply

Restore table from Flashback in Oracle

You can flashback a dropped table from recyclebin using flashback table commands

Check the recyclebin is enabled

SQL> show parameter recyclebin
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------
recyclebin			     string	 on

Commands to flashback the table after drop commands:

-- Flashback a dropped table from recyclebin using flashback commands:
SQL> SHOW RECYCLEBIN;
SQL> FLASHBACK TABLE "BIN$Fh1VKrueZFngYw8CAAq+WA==$0" TO BEFORE DROP;
OR
SQL> FLASHBACK TABLE SCOTT.TEST TO BEFORE DROP;
--Rename table while flashing it back from recyclebin
SQL> FLASHBACK TABLE SCOTT.TEST TO BEFORE DROP RENAME TO NEW_TEST;

Example to show the use of flashback commands to recover a dropped table:

SQL> create table test (id number);
Table created.
SQL> insert into test values (1);
1 row created.
SQL> insert into test values (2);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME		     OBJECT TYPE DROP TIME
------------- ------------------------------ ----------- -------------------
TEST	      BIN$Fh1VKrueZFngYw8CAAq+WA==$0 TABLE	 2024-04-15:11:30:03
 
SQL> FLASHBACK TABLE TEST TO BEFORE DROP;
Flashback complete.
SQL> select * from test;
	ID
----------
	 1
	 2
-- As you see recyclebin having one entry for TEST that go away
SQL> show recyclebin;
SQL>

You can check from this views recycle objects:

Select * from user_recyclebin;

Leave a Reply