8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 10g » Here

Flashback Table to Before Drop (Recycle Bin)

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.

Related articles.

Enable/Disable Recycle Bin

The recycle bin is enabled by default. It can be disabled/enabled at the session or system level using the following commands.

-- Session level.
ALTER SESSION SET recyclebin = OFF;
ALTER SESSION SET recyclebin = ON;

-- System level.
ALTER SYSTEM SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = ON;

Flashback Table to Before Drop

In Oracle 10g the default action of a DROP TABLE command is to logically move the table to the recycle bin by renaming it, rather than actually dropping it. The DROP TABLE ... PURGE option can be used to permanently drop a table.

DROP TABLE my_table PURGE;

The recycle bin is a logical collection of previously dropped objects, with access tied to the DROP privilege. The contents of the recycle bin can be shown using the SHOW RECYCLEBIN command and purged using the PURGE TABLE command. As a result, a previously dropped table can be recovered from the recycle bin.

Create a test table.

CREATE TABLE flashback_drop_test (
  id  NUMBER(10)
);

INSERT INTO flashback_drop_test (id) VALUES (1);
COMMIT;

Drop the table without the PURGE keyword, and check the contents of the recycle bin using the SHOW RECYCLEBIN command.

DROP TABLE flashback_drop_test;

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>

A privileged user can display all recycle bins using the SHOW DBA_RECYCLEBIN command.

Restore the dropped table using the FLASHBACK TABLE command.

FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

SELECT * FROM flashback_drop_test;

        ID
----------
         1

SQL>

Tables in the recycle bin can be queried like any other table. The following example drops the table again, and queries the renamed table from the recycle bin.

DROP TABLE flashback_drop_test;

SHOW RECYCLEBIN

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_T BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0 TABLE        2004-03-29:11:18:39
EST

SQL>


SELECT * FROM "BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0";

        ID
----------
         1

SQL>

If an object is dropped and recreated multiple times all dropped versions will be kept in the recycle bin, subject to space. Where multiple versions are present it's best to reference the tables via the RECYCLEBIN_NAME. For any references to the ORIGINAL_NAME it is assumed the most recent object is drop version in the referenced question. During the flashback operation the table can be renamed.

FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test_old;

Purge Objects From the Recycle Bin

Several purge options exist.

PURGE TABLE tablename;                   -- Specific table.
PURGE INDEX indexname;                   -- Specific index.
PURGE TABLESPACE ts_name;                -- All tables in a specific tablespace.
PURGE TABLESPACE ts_name USER username;  -- All tables in a specific tablespace for a specific user.
PURGE RECYCLEBIN;                        -- The current users entire recycle bin.
PURGE DBA_RECYCLEBIN;                    -- The whole recycle bin.

Restrictions

Several restrictions apply relating to the recycle bin.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.