Using Flashback Drop
and Managing the Recycle Bin
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 in error, it can be recovered at a later time. This feature is
called Flashback Drop, and the FLASHBACK TABLE statement is used to restore the
table. Before discussing the use of the FLASHBACK TABLE statement for this
purpose, it is important to understand how the recycle bin works, and how you
manage its contents.
This section contains the following topics:
• What Is
the Recycle Bin?
• Viewing
and Querying Objects in the Recycle Bin
• Purging
Objects in the Recycle Bin
• Restoring
Tables from the Recycle Bin
What Is the Recycle Bin?
The recycle bin is actually a data dictionary table
containing information about dropped objects. Dropped tables and any associated
objects such as indexes, constraints, nested tables, and the likes are not
removed and still occupy space. They continue to count against user space
quotas, until specifically purged from the recycle bin or the unlikely
situation where they must be purged by the database because of tablespace space
constraints.
Each user can be thought of as having his own recycle bin,
since unless a user has the SYSDBA privilege, the only objects that the user
has access to in the recycle bin are those that the user owns. A user can view
his objects in the recycle bin using the following statement:
SELECT * FROM RECYCLEBIN;
When you drop a tablespace including its contents, the
objects in the tablespace are not placed in the recycle bin and the database
purges any entries in the recycle bin for objects located in the tablespace.
The database also purges any recycle bin entries for objects in a tablespace
when you drop the tablespace, not including contents, and the tablespace is
otherwise empty. Likewise:
• When you
drop a user, any objects belonging to the user are not placed in the recycle
bin and any objects in the recycle bin are purged.
• When you
drop a cluster, its member tables are not placed in the recycle bin and any
former member tables in the recycle bin are purged.
• When you
drop a type, any dependent objects such as subtypes are not placed in the
recycle bin and any former dependent objects in the recycle bin are purged.
Object Naming in the Recycle Bin
When a 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 occur under the following circumstances:
• A user
drops a table, re-creates it with the same name, then drops it again.
• Two
users have tables with the same name, and both users drop their tables.
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
You can enable and disable the recycle bin with the
recyclebin initialization parameter. When the recycle bin is enabled, dropped
tables and their dependent objects are placed in the recycle bin. When the
recycle bin is disabled, dropped tables and their dependent objects are not
placed in the recycle bin; they are just dropped, and you must use other means
to recover them (such as recovering from backup).
The recycle bin is enabled by default.
To disable the recycle bin:
• Issue
one of the following statements:
• ALTER
SESSION SET recyclebin = OFF;
•
• ALTER
SYSTEM SET recyclebin = OFF;
To enable the recycle bin:
• Issue
one of the following statements:
• ALTER
SESSION SET recyclebin = ON;
•
• ALTER
SYSTEM SET recyclebin = ON;
Enabling and disabling the recycle bin with an ALTER SYSTEM
or ALTER SESSION statement takes effect immediately. Disabling the recycle bin
does not purge or otherwise affect objects already in the recycle bin.
Like any other initialization parameter, you can set the
initial value of the recyclebin parameter in the text initialization file
initSID.ora:
recyclebin=on
See Also:
"About Initialization Parameters and Initialization
Parameter Files" for more information on initialization parameters.
Viewing and Querying Objects in the Recycle Bin
Oracle Database provides two views for obtaining information
about objects in the recycle bin:
View Description
USER_RECYCLEBIN This
view can be used by users to see their own dropped objects in the recycle bin.
It has a synonym RECYCLEBIN, for ease of use.
DBA_RECYCLEBIN This
view gives administrators visibility to all dropped objects in the recycle bin
One use for these views is to identify the name that the
database has assigned to a dropped object, as shown in the following example:
SELECT object_name, original_name FROM dba_recyclebin
WHERE owner = 'HR';
OBJECT_NAME ORIGINAL_NAME
------------------------------
--------------------------------
BIN$yrMKlZaLMhfgNAgAIMenRA==$0 EMPLOYEES
You can also view the contents of the recycle bin using the
SQL*Plus command SHOW RECYCLEBIN.
SQL> show recyclebin
ORIGINAL NAME
RECYCLEBIN NAME
OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------
-------------------
EMPLOYEES
BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE 2003-10-27:14:00:19
You can query objects that are in the recycle bin, just as
you can query other objects. However, you must specify the name of the object
as it is identified in the recycle bin. For example:
SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0";
Purging Objects in the Recycle Bin
If you decide that you are never going to restore an item
from the recycle bin, you can use the PURGE statement to remove the items and
their associated objects from the recycle bin and release their storage space.
You need the same privileges as if you were dropping the item.
When you use the PURGE statement to purge a table, you can
use the name that the table is known by in the recycle bin or the original name
of the table. The recycle bin name can be obtained from either the DBA_ or
USER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the
Recycle Bin". The following hypothetical example purges the table
hr.int_admin_emp, which was renamed to BIN$jsleilx392mk2=293$0 when it was
placed in the recycle bin:
PURGE TABLE BIN$jsleilx392mk2=293$0;
You can achieve the same result with the following
statement:
PURGE TABLE int_admin_emp;
You can use the PURGE statement to purge all the objects in
the recycle bin that are from a specified tablespace or only the tablespace
objects belonging to a specified user, as shown in the following examples:
PURGE TABLESPACE example;
PURGE TABLESPACE example USER oe;
Users can purge the recycle bin of their own objects, and
release space for objects, by using the following statement:
PURGE RECYCLEBIN;
If you have the SYSDBA privilege, then you can purge the
entire recycle bin by specifying DBA_RECYCLEBIN, instead of RECYCLEBIN in the
previous statement.
You can also use the PURGE statement to purge an index from
the recycle bin or to purge from the recycle bin all objects in a specified
tablespace.
See Also:
Oracle Database SQL Language Reference for more information
on the PURGE statement
Restoring Tables from the Recycle Bin
Use the FLASHBACK TABLE ... TO BEFORE DROP statement to
recover objects from the recycle bin. You can specify either the name of the
table in the recycle bin or the original table name. An optional RENAME TO
clause lets you rename the table as you recover it. The recycle bin name can be
obtained from either the DBA_ or USER_RECYCLEBIN view as shown in "Viewing
and Querying Objects in the Recycle Bin". To use the FLASHBACK TABLE ...
TO BEFORE DROP statement, you need the same privileges you need to drop the
table.
The following example restores int_admin_emp table and
assigns to it a new name:
FLASHBACK TABLE int_admin_emp TO BEFORE DROP
RENAME TO
int2_admin_emp;
The system-generated recycle bin name is very useful if you
have dropped a table multiple times. For example, suppose you have three
versions of the int2_admin_emp table in the recycle bin and you want to recover
the second version. You can do this by issuing two FLASHBACK TABLE statements,
or you can query the recycle bin and then flashback to the appropriate
system-generated name, as shown in the following example. Including the create
time in the query can help you verify that you are restoring the correct table.
SELECT object_name, original_name, createtime FROM
recyclebin;
OBJECT_NAME ORIGINAL_NAME CREATETIME
------------------------------ ---------------
-------------------
BIN$yrMKlZaLMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP 2006-02-05:21:05:52
BIN$yrMKlZaVMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP 2006-02-05:21:25:13
BIN$yrMKlZaQMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP 2006-02-05:22:05:53
FLASHBACK TABLE BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TO BEFORE
DROP;
Restoring Dependent Objects
When you restore a table from the recycle bin, dependent
objects such as indexes do not get their original names back; they retain their
system-generated recycle bin names. You must manually rename dependent objects
if you want to restore their original names. If you plan to manually restore
original names for dependent objects, ensure that you make note of each
dependent object's system-generated recycle bin name before you restore the
table.
The following is an example of restoring the original names
of some of the indexes of the dropped table JOB_HISTORY, from the HR sample
schema. The example assumes that you are logged in as the HR user.
1. After
dropping JOB_HISTORY and before restoring it from the recycle bin, run the
following query:
2. SELECT
OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;
3.
4. OBJECT_NAME ORIGINAL_NAME TYPE
5. ------------------------------
------------------------- --------
6. BIN$DBo9UChtZSbgQFeMiAdCcQ==$0
JHIST_JOB_IX INDEX
7. BIN$DBo9UChuZSbgQFeMiAdCcQ==$0
JHIST_EMPLOYEE_IX INDEX
8. BIN$DBo9UChvZSbgQFeMiAdCcQ==$0
JHIST_DEPARTMENT_IX INDEX
9. BIN$DBo9UChwZSbgQFeMiAdCcQ==$0
JHIST_EMP_ID_ST_DATE_PK INDEX
10. BIN$DBo9UChxZSbgQFeMiAdCcQ==$0
JOB_HISTORY TABLE
11. Restore
the table with the following command:
12. FLASHBACK
TABLE JOB_HISTORY TO BEFORE DROP;
13. Run the
following query to verify that all JOB_HISTORY indexes retained their
system-generated recycle bin names:
14. SELECT
INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'JOB_HISTORY';
15.
16. INDEX_NAME
17. ------------------------------
18. BIN$DBo9UChwZSbgQFeMiAdCcQ==$0
19. BIN$DBo9UChtZSbgQFeMiAdCcQ==$0
20. BIN$DBo9UChuZSbgQFeMiAdCcQ==$0
21. BIN$DBo9UChvZSbgQFeMiAdCcQ==$0
22. Restore
the original names of the first two indexes as follows:
23. ALTER
INDEX "BIN$DBo9UChtZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_JOB_IX;
ALTER INDEX "BIN$DBo9UChuZSbgQFeMiAdCcQ==$0"
RENAME TO JHIST_EMPLOYEE_IX;
No comments:
Post a Comment