FLASHBACK DROP TABLE (RECYCLE BIN)

FLASHBACK DROP TABLE

The recycle bin is a logical collection of previously dropped objects. This feature doesn’t use flashback logs or undo, so it is distinct from the other flashback technologies.

Enable/Disable Recycle Bin

SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF

SQL> alter session set recyclebin = on;

Session altered.

SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      ON

 (RECYCLE BIN)

SQL> CREATE TABLE flashback_drop_test (id  NUMBER(10));

Table created.

SQL> INSERT INTO flashback_drop_test (id) VALUES (1);

1 row created.

SQL> commit;

Commit complete.


SQL> DROP TABLE flashback_drop_test;

Table dropped.

SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_T BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE        2004-03-29:11:09:07
EST

SQL> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

SELECT * FROM flashback_drop_test;

        ID
----------
         1
Rename Table

SQL> show RECYCLEBIN;

ORIGINAL NAME	 RECYCLEBIN NAME		OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
S		 BIN$x3mKgoseD/7gUxkBqMAnAA==$0 TABLE	     2021-07-19:17:20:17
SQL> create table s(n number);

Table created.

SQL> FLASHBACK TABLE s TO BEFORE DROP;
FLASHBACK TABLE s TO BEFORE DROP
*
ERROR at line 1:
ORA-38312: original name is used by an existing object


SQL> FLASHBACK TABLE s TO BEFORE DROP rename to b;

Flashback complete.

SQL> select * from b;

no rows selected

SQL> show RECYCLEBIN;

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8