Friday , July 21 2017
Home / Oracle DBA / Oracle 10G Recylce Bin Features

Oracle 10G Recylce Bin Features

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 table space constraints. Each user can be thought of as having his own recycle bin, because, unless a user has the SYS DBA 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 RECYCLE BIN;
When you drop a table space including its contents, the objects in the table space are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the table space. 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 sub types 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
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). Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin. The recycle bin is enabled by default. You enable and disable the recycle bin by changing the recycle bin initialization parameter. This parameter is not dynamic, so a database restart is required when you change it with an ALTER SYSTEM statement.
To disable the recycle bin:
1. Issue one of the following statements:
ALTER SESSION SET recycle bin = OFF;
ALTER SYSTEM SET recycle bin = OFF SCOPE = SP FILE;
2. If you used ALTER SYSTEM, restart the database.
To enable the recycle bin:
1. Issue one of the following statements:
ALTER SESSION SET recycle bin = ON;
ALTER SYSTEM SET recycle bin = ON SCOPE = SP FILE;
2. If you used ALTER SYSTEM, restart the database.
Viewing and Querying Objects in the Recycle Bin
Oracle Database provides two views for obtaining information about 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 _recycle bin WHERE owner = ‘HR’;
View Description
USER_RECYCLE BIN This view can be used by users to see their own dropped objects in the recycle bin. It has a synonym RECYCLE BIN, for ease of use. DBA_RECYCLE BIN This view gives administrators visibility to all dropped objects in the recycle bin
OBJECT_NAME ORIGINAL_NAME
—————————— ——————————–
BIN$yrMKlZaLMhfgNAgAIMenRA==$0 EMPLOYEES
You can also view the contents of the recycle bin using the SQL*Plus command SHOW RECYCLE BIN.
SQL> show recycle bin
ORIGINAL NAME RECYCLE BIN 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_RECYCLE BIN view. The following are the commands you can use:
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 table space or only the table space objects belonging to a specified user, as shown in the following examples:
PURGE TABLE SPACE example;
PURGE TABLE SPACE example USER oe;
Users can purge the recycle bin of their own objects, and release space for objects, by using the following statement:
PURGE RECYCLE BIN;
If you have the SYS DBA privilege, then you can purge the entire recycle bin by specifying DBA_RECYCLE BIN, instead of RECYCLE BIN in the previous statement. You can also use the PURGE statement to purge an index from the recycle bin or top urge from the recycle bin all objects in a specified table space.
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_RECYCLE BIN view. 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, create time FROM recycle bin;
OBJECT_NAME ORIGINAL_NAME CREATE TIME
—————————— ————— ——————-
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:
SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLE BIN;
OBJECT_NAME ORIGINAL_NAME TYPE
—————————— ————————- ——–
BIN$DBo9UChtZSbgQFeMiAdCcQ==$0 JHIST_JOB_IX INDEX
BIN$DBo9UChuZSbgQFeMiAdCcQ==$0 JHIST_EMPLOYEE_IX INDEX
BIN$DBo9UChvZSbgQFeMiAdCcQ==$0 JHIST_DEPARTMENT_IX INDEX
BIN$DBo9UChwZSbgQFeMiAdCcQ==$0 JHIST_EMP_ID_ST_DATE_PK INDEX
BIN$DBo9UChxZSbgQFeMiAdCcQ==$0 JOB_HISTORY TABLE
2. Restore the table with the following command:
FLASHBACK TABLE JOB_HISTORY TO BEFORE DROP;
3. Run the following query to verify that all JOB_HISTORY indexes retained their system-generated recycle bin names:
SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = ‘JOB_HISTORY’;
INDEX_NAME
——————————
BIN$DBo9UChwZSbgQFeMiAdCcQ==$0
BIN$DBo9UChtZSbgQFeMiAdCcQ==$0
BIN$DBo9UChuZSbgQFeMiAdCcQ==$0
BIN$DBo9UChvZSbgQFeMiAdCcQ==$0
4. Restore the original names of the first two indexes as follows:
ALTER INDEX “BIN$DBo9UChtZSbgQFeMiAdCcQ==$0” RENAME TO JHIST_JOB_IX;
ALTER INDEX “BIN$DBo9UChuZSbgQFeMiAdCcQ==$0” RENAME TO JHIST_EMPLOYEE_IX;
Note that double quotes are required around the system-generated names.
View More:
Server Result Cache
Overview of the System Global Area
ADR Structure

Comments

comments

Check Also

DATA BASE LINKS

DATABASE LINKS: What are the db links? create a link from one database to another …

Leave a Reply

Your email address will not be published. Required fields are marked *