Table Dropped in Oracle Database ? Worry not, we can recover it

There are a couple of options we can use to recover dropped tables in 19c Oracle database version 

Restore table from Recycle Bin (assume not purged)

To recover the table using the recycle bin method, the recycle bin should be enabled 

Check current value 

SHOW PARAMETER recyclebin;

If this is not enabled, enable it using commands below

ALTER SYSTEM SET recyclebin = ON SCOPE=SPFILE;

 

Shutdown immediate

startup

 

drop table techno_user.test_table;

Check if table exists in recyclebin after drop 

col owner for a20

col owner for a15

col object_name for a30

col ORIGINAL_NAME for a10

set line 120

SELECT owner, object_name, original_name, type, droptime

FROM dba_recyclebin WHERE original_name = 'TEST_TABLE' and owner='TECHNO_USER' ;

 

OWNER           OBJECT_NAME                    ORIGINAL_N TYPE                      DROPTIME

--------------- ------------------------------ ---------- ------------------------- -------------------

TECHNO_USER     BIN$JtPeMal0R/Wwtmwyqoos3g==$0 TEST_TABLE TABLE                     2026-01-11:11:06:16

Check if you want to recover only a row or a table

SELECT * FROM techno_user."BIN$JtPeMal0R/Wwtmwyqoos3g==$0";

Restore it to either original value or you can rename it while restoring 

SQL> FLASHBACK TABLE techno_user.test_table TO BEFORE DROP;

Flashback complete.

SQL> FLASHBACK TABLE techno_user.test_table TO BEFORE DROP RENAME TO TEST_TABLE_BKP ;

Flashback complete.