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(*)
----------------------------------------
                                    4000

We 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s