3:43 AM
Unknown
This article is aimed at explaining the ‘Flashback drop’ option and managing the ‘Oracle Recycle Bin’.
Flashback drop – A quick intro:
• Have you ever thought of restoring a table which is dropped accidently?
• When you drop a table, the database does not immediately remove the space associated with the table.
• The database renames the table and places it and any associated objects in a ‘recycle bin’, where, in case the table was dropped accidently, it can be recovered at a later time. (Very similar to our ‘Restore’ from Windows Recycle bin)
• This feature is called Flashback Drop, and the ‘FLASHBACK TABLE’ statement is used to restore the table. This was introduced as a part of Oracle 10g.
Oracle Recycle Bin – A quick intro:
• The recycle bin is actually a ‘data dictionary table’ containing information about dropped objects.
• Dropped tables and any associated objects such as indexes, constraints etc. remain in the recycle bin.
• They continue to remain so, until specifically ‘purged’ from the recycle bin or the unlikely situation where they must be purged by the database because of table space constraints.
• Each user can be thought of as having his/her own recycle bin and he/she can view his/her dropped objects by using
SELECT * FROM RECYCLEBIN
What happens when a table is dropped?
• Dropped table is moved to the recycle bin.
• The table and its associated objects are given system-generated names. This is necessary to avoid name conflicts that may arise if multiple tables have the same name.
• This could happen when a user drops a table, re-creates it with the same name, and drops it again.
• The renaming convention is as follows:
BIN$unique_id$version
where:
unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases
version is a version number assigned by the database.
Enabling and Disabling the Recycle Bin:
• The recycle bin is ‘enabled’ by default. The initialization parameter RECYCLEBIN can be used to explicitly enable or disable the recycle bin by providing ON/OFF values.
To enable the recycle bin:
ALTER SESSION SET RECYCLEBIN=ON; -- specific for that session
ALTER SYSTEM SET RECYCLEBIN=ON;-- for the entire database
To disable the recycle bin:
ALTER SESSION SET RECYCLEBIN=OFF; -- specific for that session
ALTER SYSTEM SET RECYCLEBIN=OFF;-- for the entire database
Viewing and Querying objects from the Recycle Bin:
•Say we have dropped our ‘TEST1234’ table, we can get to know the name of the table in recycle bin by using
SELECT object_name AS recycle_name,original_name,type FROM RECYCLEBIN
WHERE original_name= ‘TEST1234’;
•We can directly view the contents of the dropped table by using the ‘system generated name’
SELECT * FROM "BIN$DMA758TvVdLgUyvulAqPqA==$0";
(Note the use of quotes due to the special characters in the recycle bin name)
Restoring an object from the Recycle Bin:
•We can either use the recycle_name to restore back the dropped tables,
FLASHBACK TABLE " BIN$DMA758TvVdLgUyvulAqPqA==$0" TO BEFORE DROP;
•Or the Original name as well
FLASHBACK TABLE TEST1234 TO BEFORE DROP RENAME TO TEST1234_bkp;
Pre-requisites:
•Recycle bin should have been enabled for that session/system.
•Be granted with the FLASHBACK TABLE or FLASHBACK ANY TABLE privilege (Generally, they are enabled by default)
Limitations:
•While Oracle permits queries against objects stored in the recycle bin, we cannot use DML or DDL statements on objects in the recycle bin.
•When space pressure arises, the database selects objects for automatic purging from the recycle bin on a first-in, first-out basis. That is, the objects dropped first are the ones first selected for purging.
Flashback drop – A quick intro:
• Have you ever thought of restoring a table which is dropped accidently?
• When you drop a table, the database does not immediately remove the space associated with the table.
• The database renames the table and places it and any associated objects in a ‘recycle bin’, where, in case the table was dropped accidently, it can be recovered at a later time. (Very similar to our ‘Restore’ from Windows Recycle bin)
• This feature is called Flashback Drop, and the ‘FLASHBACK TABLE’ statement is used to restore the table. This was introduced as a part of Oracle 10g.
Oracle Recycle Bin – A quick intro:
• The recycle bin is actually a ‘data dictionary table’ containing information about dropped objects.
• Dropped tables and any associated objects such as indexes, constraints etc. remain in the recycle bin.
• They continue to remain so, until specifically ‘purged’ from the recycle bin or the unlikely situation where they must be purged by the database because of table space constraints.
• Each user can be thought of as having his/her own recycle bin and he/she can view his/her dropped objects by using
SELECT * FROM RECYCLEBIN
What happens when a table is dropped?
• Dropped table is moved to the recycle bin.
• The table and its associated objects are given system-generated names. This is necessary to avoid name conflicts that may arise if multiple tables have the same name.
• This could happen when a user drops a table, re-creates it with the same name, and drops it again.
• The renaming convention is as follows:
BIN$unique_id$version
where:
unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases
version is a version number assigned by the database.
Enabling and Disabling the Recycle Bin:
• The recycle bin is ‘enabled’ by default. The initialization parameter RECYCLEBIN can be used to explicitly enable or disable the recycle bin by providing ON/OFF values.
To enable the recycle bin:
ALTER SESSION SET RECYCLEBIN=ON; -- specific for that session
ALTER SYSTEM SET RECYCLEBIN=ON;-- for the entire database
To disable the recycle bin:
ALTER SESSION SET RECYCLEBIN=OFF; -- specific for that session
ALTER SYSTEM SET RECYCLEBIN=OFF;-- for the entire database
Viewing and Querying objects from the Recycle Bin:
•Say we have dropped our ‘TEST1234’ table, we can get to know the name of the table in recycle bin by using
SELECT object_name AS recycle_name,original_name,type FROM RECYCLEBIN
WHERE original_name= ‘TEST1234’;
•We can directly view the contents of the dropped table by using the ‘system generated name’
SELECT * FROM "BIN$DMA758TvVdLgUyvulAqPqA==$0";
(Note the use of quotes due to the special characters in the recycle bin name)
Restoring an object from the Recycle Bin:
•We can either use the recycle_name to restore back the dropped tables,
FLASHBACK TABLE " BIN$DMA758TvVdLgUyvulAqPqA==$0" TO BEFORE DROP;
•Or the Original name as well
FLASHBACK TABLE TEST1234 TO BEFORE DROP RENAME TO TEST1234_bkp;
Pre-requisites:
•Recycle bin should have been enabled for that session/system.
•Be granted with the FLASHBACK TABLE or FLASHBACK ANY TABLE privilege (Generally, they are enabled by default)
Limitations:
•While Oracle permits queries against objects stored in the recycle bin, we cannot use DML or DDL statements on objects in the recycle bin.
•When space pressure arises, the database selects objects for automatic purging from the recycle bin on a first-in, first-out basis. That is, the objects dropped first are the ones first selected for purging.
