CAN WE FLASHBACK A TABLE LAST BEFORE DROP IN ORACLE?
We have a table called customers with 3000 rows.
kish@exdbx<>select count(*) from test.customers;
COUNT(*)
----------------------------------------
3000
I dropped the table as part of testing
kish@exdbx<>drop table test.customers;
Table dropped.
kish@exdbx<>select * from test.customers;
select * from test.customers
*
ERROR at line 1:
ORA-00942: table or view does not exist
If flashback is enabled, we can recover the table back to previous state until last commit or last state of table
kish@exdbx<>select OWNER,OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE,DROPTIME,DROPSCN,CAN_UNDROP,CAN_PURGE from dba_recyclebin where ORIGINAL_NAME='CUSTOMERS';
OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE DROPTIME DROPSCN CAN_UNDROP CAN_PURGE
------------------------------ --------------- --------------- --------- ------------------------- --------------- ---------- --------------- ---------------
TEST BIN$yNUa7Av5W8D CUSTOMERS DROP TABLE 2021-08-05:23:5 23389767 YES YES
gUyQ4qMCb0g==$0 9:54
TEST BIN$yOj/IWTHMlz CUSTOMERS DROP TABLE 2021-08-06:23:4 23642299 YES YES
gUyQ4qMCfyw==$0 3:47
Use below to flashback to previous state
kish@exdbx<>flashback table customers to before drop;
Flashback complete.
Now i got my table back !
kish@exdbx<>select count(*) from test.customers;
COUNT(*)
----------------------------------------
3000
Again i added 1000 more rows to the table
kish@exdbx<>insert /*+append parallel(8) */ into test.customers (cname,cid,pincode,city) select dbms_random.string('a','10'),cust_id.nextval,dbms_random.random,decode(round(dbms_random.value(0,9)),0,'Berlin',1,'California',2,'London',3,'Lisbon',4,'Paris',5,'Rome',6,'Delhi',7,'Moscow',8,'Canberra',9,'New York')
from dual connect by level <= 1000 2 ;
1000 rows created.
Elapsed: 00:00:00.42
kish@exdbx<>commit;
Commit complete.
Currently i got 4000 rows in table customers
kish@exdbx<>select count(*) from customers;
COUNT(*)
----------------------------------------
4000
Drop the table again
kish@exdbx<>drop table test.customers;
Table dropped.
select * from test.customers
*
ERROR at line 1:
ORA-00942: table or view does not exist
Check the recyclebin for the table while drop details
kish@exdbx<>select OWNER,OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE,DROPTIME,DROPSCN,CAN_UNDROP,CAN_PURGE from dba_recyclebin where ORIGINAL_NAME='CUSTOMERS';
OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE DROPTIME DROPSCN CAN CAN
------------------------------ ------------------------------ -------------------------------- --------- ------------------------- ------------------- ---------------------------------------- --- ---
TEST BIN$yNUa7Av5W8DgUyQ4qMCb0g==$0 CUSTOMERS DROP TABLE 2021-08-05:23:59:54 23389767 YES YES
TEST BIN$yOlxK8SXPJXgUyQ4qMAY9g==$0 CUSTOMERS DROP TABLE 2021-08-07:00:15:40 23763980 YES YES
Note that we cannot go back to the previous before state with 3000 rows in the table as i modified table by adding new rows
kish@exdbx<>flashback table customers to before drop;
Flashback complete.
I got the table with last recently modified state
kish@exdbx<>select count(*) from customers;
COUNT(*)
----------------------------------------
4000We can also flashback the table to different name
kish@exdbx<>flashback table test.customers to before drop rename to afterdrop;
Flashback complete.
Elapsed: 00:00:00.05
kish@exdbx<>select count(*) from test.afterdrop;
COUNT(*)
----------------------------------------
4000