8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- Enable/Disable Recycle Bin
- Flashback Table to Before Drop
- Purge Objects From the Recycle Bin
- Views
- Restrictions
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.
Views
In addition to the SHOW
command, you can get information about the contents of the recycle bin using the following views.
- USER_RECYCLEBIN : Displays items in the current user's recycle bin.
- DBA_RECYCLEBIN : Displays items in the recycle bin of all users in the database.
- CDB_RECYCLEBIN : When queried from the root container of a container database, it displays items in the recycle bin of all users in the containers. From any container other than the root container, it acts like the
DBA_RECYCLEBIN
view.
Restrictions
Several restrictions apply relating to the recycle bin.
- Only available for non-system, locally managed tablespaces.
- There is no fixed size for the recycle bin. The time an object remains in the recycle bin can vary. The recycle bin is cleared down if the tablespace is under space pressure. The recycle bin will not cause data files to autoextend, so in some situations objects can be purged very quickly.
- The objects in the recycle bin are restricted to query operations only (no DDL or DML).
- Flashback query operations must reference the recycle bin name.
- Tables and all dependent objects are placed into, recovered and purged from the recycle bin at the same time.
- Tables with Fine Grained Access policies aer not protected by the recycle bin.
- Partitioned index-organized tables are not protected by the recycle bin.
- The recycle bin does not preserve referential integrity.
For more information see:
- Oracle Flashback Drop: Undo a DROP TABLE Operation
- Flashback New Features and Enhancements in Oracle Database 10g
Hope this helps. Regards Tim...