Sign in or 
| Version | User | Scope of changes |
|---|---|---|
| Sep 12 2008, 5:53 PM EDT (current) | juancarlosreyesp | 3143 words added |
| Sep 12 2008, 5:52 PM EDT | juancarlosreyesp |
Changes
Key: Additions Deletions
1 DROP TABLE flashback
Once a table is dropped the table is renamed and, along with some dependent objects, and placed in the Recycle Bin.
Dependent objects of the table which are stored in the recycle bin include indexes, constraints (excluding foreign constraints), triggers, nested tables, LOB segments and LOB index segments.
When you perform Flashback Drop, the objects are generally all retrieved together. It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.
Recycle Bin keeps dropped objects until the space they occupied is needed to store new data.
Objects in the recycle bin continue to count against user space quotas.
Recycle bin objects are not counted as used space.
SELECT * FROM "BIN$KSD8DB9L345KLA==$0";
are given system-generated names. To avoid name conflicts for dropped objects recycle bin.
Object names are formed as follows:
(Database backup and recovery basics says)
BIN$$globalUID$version where:
· globalUID is a globally unique, 24 character long identifier generated for the object.
· version is a version number assigned by the database
The recycle bin name of an object is always 30 characters long.
Note that the globalUID used in the recycle bin name is not readily correlated with
any externally visible piece of information about the object or the database.
(Oracle Database Administrator Guide 10g Release 2 says)
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
The rules that govern how long an object is retained in the recycle bin and how and when space is reclaimed are:
When space pressure arises, the database selects objects for automatic purging from the recycle bin. Objects are selected for purging on a first-in, first-out basis, that is, the first objects dropped are the first selected for purging.
The databases purges the minimum possible number of objects selected for purging to meet immediate needs for space. This policy serves two purposes:
· It minimizes the performance penalty on transactions that encounter space pressure, by not performing more purge operations than are required;
· It maximizes the length of time objects remain in the recycle bin, by leaving them there until space is needed.
Oracle never automatically reclaims space or overwrites objects in the recycle bin unless forced to do so in response to space pressure.
Dropped objects are kept in the recycle bin until such time as no new extents can be allocated in the tablespace to which the objects belong without growing the tablespace.
Space pressure can also arise due to user quotas defined for a particular tablespace. A tablespace may have free space, but the user may have exhausted his or her quota.
Dependent objects such as indexes on a table are selected for purging before the associated table (or other required segment).
For AUTO EXTEND-able tablespaces, objects are purged from the recycle bin to reclaim space before datafiles are extended.
will all be stored in the recycle bin. For example, consider these SQL statements:
CREATE TABLE EMP ( ...columns ); # EMP version 1
DROP TABLE EMP;
CREATE TABLE EMP ( ...columns ); # EMP version 2
DROP TABLE EMP;
CREATE TABLE EMP ( ...columns ); # EMP version 3
DROP TABLE EMP;
In such a case, each table EMP is assigned a unique name in the recycle bin when it is dropped. You can use a FLASHBACK TABLE... TO BEFORE DROP statement with the original name of the table, as shown in this example:
FLASHBACK TABLE EMP TO BEFORE DROP;
The most recently dropped table with that original name is retrieved from the recycle
bin, with its original name. You can retrieve it and assign it a new name using a
RENAME TO clause.
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_3;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_2;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_1;
You can also retrieve any table you want from the recycle bin, regardless of any
collisions among original names, by using the table's unique recycle bin name.
In the same way when you use the PURGE TABLE statement the first table dropped will be the one to be purged.
PURGE TABLE EMP; # version 1 of the table is purged
PURGE TABLE EMP; # version 2 of the table is purged
PURGE TABLE EMP; # version 3 of the table is purged
DROP TABLE table_name;
Moves the table to the recycle bin.
DROP TABLE table_name PURGE;
If you specify PURGE, then the database does not place the table and its dependent objects into the recycle bin.
When you drop a partitioned table with the PURGE keyword. If any of the subtransactions fails, the only operation allowed on the table is another DROP TABLE ... PURGE statement.
Such a statement will resume work from where the previous DROP TABLE statement failed, assuming that you have corrected any errors that the previous operation encountered.
Use the PURGE statement to remove permanently (flashback is not feasible) a table or index from your recycle bin and release all of the space associated with the object, or to remove the entire recycle bin, or to remove part of all of a dropped tablespace from the recycle bin.
System-generated recycle bin object names are unique. Therefore, if you specify
the system-generated name, then the database purges that specified object.
When the database purges a table, purges too all its dependent objects.
RECYCLEBIN Use this clause to purge the current user's recycle bin. DBA_RECYCLEBIN This clause is valid only if you have SYSDBA system privilege. It lets you remove all objects from the system-wide recycle bin.
TABLESPACE to purge all the objects residing in the specified tablespace from the
recycle bin.
USER to reclaim space in a tablespace for a specified user.
PURGE RECYCLEBIN;
PURGE TABLE EMP;
PURGE TABLE "BIN$KSD8DB9L345KLA==$0";
PURGE INDEX "BIN$GTE72KJ22H9==$0";
PURGE TABLESPACE hr;
PURGE TABLESPACE hr USER scott;
PURGE DBA_RECYCLEBIN;
TO BEFORE DROP Clause
Use this clause to retrieve from the recycle bin a table that has been dropped, along with all possible dependent objects.
The recycle bin operates at the object level, in terms of tables, indexes, and so on. An object may have multiple segments associated with it, such as partitioned tables, partitioned indexes, lob segments, nested tables, and so on. Because the database reclaims only the segments needed to immediately satisfy space pressure, it can happen that some but not all segments of an object are reclaimed. When this happens, any segments of the object not reclaimed immediately are marked as temporary segments. These temporary segments are the first candidates to be reclaimed the next time space pressure arises. In such a case, the partially-reclaimed object can no longer be removed from the recycle bin with Flashback Drop. (For example, if one partition of a partitioned table is reclaimed, the table can no longer be the object of a Flashback Drop.)
If you specify the user-specified name, and if the recycle bin contains more than
one object of that name, then the database retrieves the object that was moved to
the recycle bin most recently. If you want to retrieve an older version of the table,
do one of these things:
· Specify the system-generated recycle bin name of the table you want toretrieve.
· Issue additional FLASHBACK TABLE ... TO BEFORE DROP statements until you retrieve the table you want.
Oracle Database attempts to preserve the original table name. If a new table of the
same name has been created in the same schema since the original table was dropped, then the database returns an error unless you also specify the RENAME TO clause.
The retrieved indexes, triggers, and constraints have recycle bin names. Therefore
it is advisable to query the USER_RECYCLEBIN view before issuing a FLASHBACK TABLE ... TO BEFORE DROP statement so that you can rename the retrieved triggers and constraints to get more usable names.
FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;
Note the use of quotes, due to the possibility of special characters appearing in the
recycle bin object names.You can also use the table's original name in the Flashback
Drop operation:
FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;
FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP
RENAME TO hr.int2_admin_emp;
Default value ON
Modifiable ALTER SESSION, ALTER SYSTEM
Basic No
Is used to control whether the Flashback Drop capability is turned on or off. If the parameter is set to OFF, then dropped tables do not go into the recycle bin.
Any user with drop privileges over the object can drop the object, placing it in the
recycle bin.
Any user who can drop an object can
· Perform Flashback Drop to retrieve the dropped object from the recycle
(FLASHBACK TABLE... TO BEFORE DROP)
· Can purge the objects from the recycle bin. (PURGE)
· PURGE
To purge the database object must reside in your own schema or you must have the DROP ANY system privilege for the type of object to be purged, or you must have the SYSDBA system privilege.
· Query tables on the recyblebin
Users must have SELECT and FLASHBACK privileges over an object in the recycle
bin to be able to query the object in the recycle bin.
Any users who had the SELECT privilege over an object before it was dropped continue to have it
Users must have FLASHBACK privilege to query any object in the recycle bin, because these are objects from a past state of the database.
· There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months.
· While Oracle permits queries against objects stored in the recycle bin, you cannot use DML or DDL statements on objects in the recycle bin.
· You can perform Flashback Query on tables in the recycle bin, but only by using the recycle bin name. You cannot use the original name of the table.
· Due to security concerns, tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.
· Bitmap join indexes are not put in the recycle bin.
· Partitioned index-organized tables are not protected by the recycle bin.
· When a tablespace is dropped including its contents, the objects in the tablespace are dropped immediately, and not placed in the recycle bin. Any objects in the recycle bin from the dropped tablespace are purged from the recycle bin.
· If all objects from a tablespace have been placed in the recycle bin, then dropping the tablespace causes the objects to be purged, even if you do not use the INCLUDING CONTENTS clause with DROP TABLESPACE.
· When a user is dropped. Any objects in the recycle bin that belonged to the user are purged from the recycle bin.
· When you drop a cluster, all tables in the cluster are purged.
· When you drop a user-defined data type, all objects directly or indirectly dependent upon that type are purged.
· When you drop a materialized view, Oracle Database does not place it in the recycle bin.
· When a table is retrieved from the Recycle Bin, all of the dependent objects for the table that are in the recycle bin are retrieved with it. They cannot be retrieved separately.
· The Oracle Database currently only supports recovering dropped objects from the recycle bin. The truncated objects cannot be recovered.
· If the source table for a synchronous change table is dropped and then restored from the recycle bin, changes are no longer captured in that change table. The publisher must create a new synchronous change table to capture future changes to the restored source table.
DBA_RECYCLEBIN Lets administrators see all dropped objects in the recycle bin
DBA_RECYCLEBIN
The difference between DBA_RECYCLEBIN and USER_RECYCLEBIN is the latter don’t have OWNER column.
Column DROPPED YES|NO|NULL
Appear in the views USER_TABLES, ALL_TABLES, DBA_TABLES, USER_INDEX, ALL_INDEX and DBA_INDEX.
DROPPED column Indicates whether the table has been dropped and is in the recycle bin (YES) or not (NO); null for partitioned tables-
Shows objects in the recycle bin that can be reverted with the FLASHBACK BEFORE DROP command.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME TYPE DROP TIME
---------------- --------------------------------- ------------ -------------------
EMPLOYEE_DEMO BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 TABLE 2005-04-11:17:08:54
HTML DB
Using HTML DB with Oracle Database 10g release 1 (10.1) or later, you can use the Recycle Bin to view and restore dropped database objects.
1. Click the SQL Workshop icon on the Workspace home page.
2. Click Utilities.
3. Click Recycle Bin.
4. Click View Objects.
5. To filter the report, select an object type, enter the object name in the Original
Name field, and click Go.
6. Click the View icon to access the Object Detail page. On this page you can:
7. To view object details, click the View icon adjacent to the original name.
On the Object Details page, you can:
· Click Restore Object to restore the current object
· Click Purge to permanently delete the current object
Emptying the Recycle Bin:
To empty the Recycle Bin without viewing the objects:
1. Click the SQL Workshop icon on the Workspace home page.
2. Click Utilities.
3. Click Recycle Bin.
4. From the Tasks list on the right side of the page, select Purge Recycle Bin.
1. In the Backup/Recovery section of the Maintenance page, select Perform Recovery.
The Perform Recovery page appears.
2.In the Object Level Recovery section, select Tables for the object type. The page reloads with options appropriate for tables in the Object Level Recovery section. For the Operation Type, select Flashback dropped tables and click Perform Object Level Recovery.
The Perform Object Level Recovery: Dropped Objects Selection page appears.
3.The Search form lets you search among the dropped objects in the Recycle Bin for the objects you want to recover. Provide values for one or both of the Schema Name and Table fields, and click Go to search.
When the page refreshes, the Results section lists the objects matching your search. If you only see the Recycle Bin listed, then click the arrow next to the Recycle Bin to expand its contents by one level, showing dropped tables matching your search but not their dependent objects. You can further expand individual tables, or click Expand All to see all objects in the Recycle Bin, including both dropped tables and dependent objects such as indexes and triggers. For each table listed, you can click View Content in the Operation column to display its contents.To select one or more tables for Flashback Drop, click the checkbox next to each table.
When you have selected all of the objects to restore, click Next.
The Perform Object Level Recovery: Rename page appears.
4.If needed, specify new names for any dropped objects you are returning to your database. The primary reason for renaming objects when you retrieve them from the recycle bin is if you have created new tables with the same names as tables being retrieved. If you need to rename some objects, then enter new names as needed in the New Name field in the list of tables being flashed back. Click Next to continue.
The Perform Object Level Recovery: Review page appears. This page displays an impact analysis, showing the full set of objects to be flashed back, including the dependent objects, as well as the names they will have when the Flashback Drop operation is complete.
5.If you are satisfied with the changes listed in the review, click Submit to perform the Flashback Drop.
A confirmation page should indicate the success of the operation.
6.Click OK to return to the database home page.
1.1 What it’s for?
· Allows to restore dropped tables.1.2 How it works
Flashback Drop is built on a mechanism called the Recycle Bin. Dropped tables are stored in the Recycle Bin.Once a table is dropped the table is renamed and, along with some dependent objects, and placed in the Recycle Bin.
Dependent objects of the table which are stored in the recycle bin include indexes, constraints (excluding foreign constraints), triggers, nested tables, LOB segments and LOB index segments.
When you perform Flashback Drop, the objects are generally all retrieved together. It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.
Recycle Bin keeps dropped objects until the space they occupied is needed to store new data.
Objects in the recycle bin continue to count against user space quotas.
Recycle bin objects are not counted as used space.
1.2.1 Executing a query against tables in the recycle bin
You can query a table from the recycle b in specifying his name:SELECT * FROM "BIN$KSD8DB9L345KLA==$0";
1.2.2 Renaming convention
When a dropped table is moved to the recycle bin, the table and its associated objectsare given system-generated names. To avoid name conflicts for dropped objects recycle bin.
Object names are formed as follows:
(Database backup and recovery basics says)
BIN$$globalUID$version where:
· globalUID is a globally unique, 24 character long identifier generated for the object.
· version is a version number assigned by the database
The recycle bin name of an object is always 30 characters long.
Note that the globalUID used in the recycle bin name is not readily correlated with
any externally visible piece of information about the object or the database.
(Oracle Database Administrator Guide 10g Release 2 says)
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
1.2.3 Recycle Bin capacity and space pressure
There is no fixed amount of space preallocated for the recycle bin. Therefore, there is no guaranteed minimum amount of time during which a dropped object will remain in the recycle bin.The rules that govern how long an object is retained in the recycle bin and how and when space is reclaimed are:
When space pressure arises, the database selects objects for automatic purging from the recycle bin. Objects are selected for purging on a first-in, first-out basis, that is, the first objects dropped are the first selected for purging.
The databases purges the minimum possible number of objects selected for purging to meet immediate needs for space. This policy serves two purposes:
· It minimizes the performance penalty on transactions that encounter space pressure, by not performing more purge operations than are required;
· It maximizes the length of time objects remain in the recycle bin, by leaving them there until space is needed.
Oracle never automatically reclaims space or overwrites objects in the recycle bin unless forced to do so in response to space pressure.
Dropped objects are kept in the recycle bin until such time as no new extents can be allocated in the tablespace to which the objects belong without growing the tablespace.
Space pressure can also arise due to user quotas defined for a particular tablespace. A tablespace may have free space, but the user may have exhausted his or her quota.
Dependent objects such as indexes on a table are selected for purging before the associated table (or other required segment).
For AUTO EXTEND-able tablespaces, objects are purged from the recycle bin to reclaim space before datafiles are extended.
1.2.4 Dropping purging several objects with the same name
You can create, and then drop, several objects with the same original name, and theywill all be stored in the recycle bin. For example, consider these SQL statements:
CREATE TABLE EMP ( ...columns ); # EMP version 1
DROP TABLE EMP;
CREATE TABLE EMP ( ...columns ); # EMP version 2
DROP TABLE EMP;
CREATE TABLE EMP ( ...columns ); # EMP version 3
DROP TABLE EMP;
In such a case, each table EMP is assigned a unique name in the recycle bin when it is dropped. You can use a FLASHBACK TABLE... TO BEFORE DROP statement with the original name of the table, as shown in this example:
FLASHBACK TABLE EMP TO BEFORE DROP;
The most recently dropped table with that original name is retrieved from the recycle
bin, with its original name. You can retrieve it and assign it a new name using a
RENAME TO clause.
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_3;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_2;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_1;
You can also retrieve any table you want from the recycle bin, regardless of any
collisions among original names, by using the table's unique recycle bin name.
In the same way when you use the PURGE TABLE statement the first table dropped will be the one to be purged.
PURGE TABLE EMP; # version 1 of the table is purged
PURGE TABLE EMP; # version 2 of the table is purged
PURGE TABLE EMP; # version 3 of the table is purged
1.3 Syntax
1.3.1 Drop Table
DROP TABLE table_name;
Moves the table to the recycle bin.
DROP TABLE table_name PURGE;
If you specify PURGE, then the database does not place the table and its dependent objects into the recycle bin.
When you drop a partitioned table with the PURGE keyword. If any of the subtransactions fails, the only operation allowed on the table is another DROP TABLE ... PURGE statement.
Such a statement will resume work from where the previous DROP TABLE statement failed, assuming that you have corrected any errors that the previous operation encountered.
1.3.2 Purge
Use the PURGE statement to remove permanently (flashback is not feasible) a table or index from your recycle bin and release all of the space associated with the object, or to remove the entire recycle bin, or to remove part of all of a dropped tablespace from the recycle bin.
System-generated recycle bin object names are unique. Therefore, if you specify
the system-generated name, then the database purges that specified object.
When the database purges a table, purges too all its dependent objects.
RECYCLEBIN Use this clause to purge the current user's recycle bin. DBA_RECYCLEBIN This clause is valid only if you have SYSDBA system privilege. It lets you remove all objects from the system-wide recycle bin.
TABLESPACE to purge all the objects residing in the specified tablespace from the
recycle bin.
USER to reclaim space in a tablespace for a specified user.
PURGE RECYCLEBIN;
PURGE TABLE EMP;
PURGE TABLE "BIN$KSD8DB9L345KLA==$0";
PURGE INDEX "BIN$GTE72KJ22H9==$0";
PURGE TABLESPACE hr;
PURGE TABLESPACE hr USER scott;
PURGE DBA_RECYCLEBIN;
1.3.3 Flashback Drop Table
TO BEFORE DROP Clause
Use this clause to retrieve from the recycle bin a table that has been dropped, along with all possible dependent objects.
The recycle bin operates at the object level, in terms of tables, indexes, and so on. An object may have multiple segments associated with it, such as partitioned tables, partitioned indexes, lob segments, nested tables, and so on. Because the database reclaims only the segments needed to immediately satisfy space pressure, it can happen that some but not all segments of an object are reclaimed. When this happens, any segments of the object not reclaimed immediately are marked as temporary segments. These temporary segments are the first candidates to be reclaimed the next time space pressure arises. In such a case, the partially-reclaimed object can no longer be removed from the recycle bin with Flashback Drop. (For example, if one partition of a partitioned table is reclaimed, the table can no longer be the object of a Flashback Drop.)
If you specify the user-specified name, and if the recycle bin contains more than
one object of that name, then the database retrieves the object that was moved to
the recycle bin most recently. If you want to retrieve an older version of the table,
do one of these things:
· Specify the system-generated recycle bin name of the table you want toretrieve.
· Issue additional FLASHBACK TABLE ... TO BEFORE DROP statements until you retrieve the table you want.
Oracle Database attempts to preserve the original table name. If a new table of the
same name has been created in the same schema since the original table was dropped, then the database returns an error unless you also specify the RENAME TO clause.
The retrieved indexes, triggers, and constraints have recycle bin names. Therefore
it is advisable to query the USER_RECYCLEBIN view before issuing a FLASHBACK TABLE ... TO BEFORE DROP statement so that you can rename the retrieved triggers and constraints to get more usable names.
FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;
Note the use of quotes, due to the possibility of special characters appearing in the
recycle bin object names.You can also use the table's original name in the Flashback
Drop operation:
FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;
FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP
RENAME TO hr.int2_admin_emp;
1.4 Database Configuration
Syntax RECYCLEBIN = [ ON | OFF ]Default value ON
Modifiable ALTER SESSION, ALTER SYSTEM
Basic No
Is used to control whether the Flashback Drop capability is turned on or off. If the parameter is set to OFF, then dropped tables do not go into the recycle bin.
1.5 Performance
1.6 Privileges
· DROPAny user with drop privileges over the object can drop the object, placing it in the
recycle bin.
Any user who can drop an object can
· Perform Flashback Drop to retrieve the dropped object from the recycle
(FLASHBACK TABLE... TO BEFORE DROP)
· Can purge the objects from the recycle bin. (PURGE)
· PURGE
To purge the database object must reside in your own schema or you must have the DROP ANY system privilege for the type of object to be purged, or you must have the SYSDBA system privilege.
· Query tables on the recyblebin
Users must have SELECT and FLASHBACK privileges over an object in the recycle
bin to be able to query the object in the recycle bin.
Any users who had the SELECT privilege over an object before it was dropped continue to have it
Users must have FLASHBACK privilege to query any object in the recycle bin, because these are objects from a past state of the database.
1.7 Examples and techniques
1.8 Restrictions and Errors
· The recycle bin functionality is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments (objects) is in a dictionary-managed tablespace, then these objects are protected by the recycle bin.· There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months.
· While Oracle permits queries against objects stored in the recycle bin, you cannot use DML or DDL statements on objects in the recycle bin.
· You can perform Flashback Query on tables in the recycle bin, but only by using the recycle bin name. You cannot use the original name of the table.
· Due to security concerns, tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.
· Bitmap join indexes are not put in the recycle bin.
· Partitioned index-organized tables are not protected by the recycle bin.
· When a tablespace is dropped including its contents, the objects in the tablespace are dropped immediately, and not placed in the recycle bin. Any objects in the recycle bin from the dropped tablespace are purged from the recycle bin.
· If all objects from a tablespace have been placed in the recycle bin, then dropping the tablespace causes the objects to be purged, even if you do not use the INCLUDING CONTENTS clause with DROP TABLESPACE.
· When a user is dropped. Any objects in the recycle bin that belonged to the user are purged from the recycle bin.
· When you drop a cluster, all tables in the cluster are purged.
· When you drop a user-defined data type, all objects directly or indirectly dependent upon that type are purged.
· When you drop a materialized view, Oracle Database does not place it in the recycle bin.
· When a table is retrieved from the Recycle Bin, all of the dependent objects for the table that are in the recycle bin are retrieved with it. They cannot be retrieved separately.
· The Oracle Database currently only supports recovering dropped objects from the recycle bin. The truncated objects cannot be recovered.
· If the source table for a synchronous change table is dropped and then restored from the recycle bin, changes are no longer captured in that change table. The publisher must create a new synchronous change table to capture future changes to the restored source table.
1.9 Oracle Dictionary
To see the contents of your recycle bin, query the USER_RECYCLEBIN data dictionary review. You can use the RECYCLEBIN synonym instead.DBA_RECYCLEBIN Lets administrators see all dropped objects in the recycle bin
DBA_RECYCLEBIN
The difference between DBA_RECYCLEBIN and USER_RECYCLEBIN is the latter don’t have OWNER column.
| Column | Datatype NULL | Description |
| OWNER | VARCHAR2(30) NOT NULL | Name of the original owner of the object |
| OBJECT_NAME | VARCHAR2(30) NOT NULL | New name of the object |
| ORIGINAL_NAME | VARCHAR2(32) | Original name of the object |
| OPERATION | VARCHAR2(9) | Operation carried out on the object DROP - Object was dropped TRUNCATE - Object was truncated |
| TYPE | VARCHAR2(25) | TABLE NORMAL INDEX BITMAP INDEX NESTED TABLE LOB LOB INDEX DOMAIN INDEX IOT TOP INDEX IOT OVERFLOW SEGMENT IOT MAPPING TABLE TRIGGER CONSTRAINT Table Partition Table Composite Partition Index Partition Index Composite Partition LOB Partition LOB Composite Partition |
| TS_NAME | VARCHAR2(30) | Name of the tablespace to which the object belongs |
| CREATETIME | VARCHAR2(19) | Timestamp for the creation of the object |
| DROPTIME | VARCHAR2(19) | Timestamp for the dropping of the object |
| DROPSCN | NUMBER | System change number (SCN) of the transaction which moved the object to the recycle bin |
| PARTITION_NAME | VARCHAR2(32) | Name of the partition which was dropped |
| CAN_UNDROP | VARCHAR2(3) | Indicates whether the object can be undropped (YES) or not (NO) |
| CAN_PURGE | VARCHAR2(3) | Indicates whether the object can be purged (YES) or not (NO) |
| RELATED | NUMBER NOT NULL | Object number of the parent object |
| BASE_OBJECT | NUMBER NOT NULL | Object number of the base object |
| PURGE_OBJECT | NUMBER NOT NULL | Object number for the object which gets purged |
| SPACE | NUMBER | Number of blocks used by the object |
Appear in the views USER_TABLES, ALL_TABLES, DBA_TABLES, USER_INDEX, ALL_INDEX and DBA_INDEX.
DROPPED column Indicates whether the table has been dropped and is in the recycle bin (YES) or not (NO); null for partitioned tables-
1.10 Other Tools
1.10.1 SQL*PLUS
command: SHOW RECYC[LEBIN] [original_name]Shows objects in the recycle bin that can be reverted with the FLASHBACK BEFORE DROP command.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME TYPE DROP TIME
---------------- --------------------------------- ------------ -------------------
EMPLOYEE_DEMO BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 TABLE 2005-04-11:17:08:54
HTML DB
Using HTML DB with Oracle Database 10g release 1 (10.1) or later, you can use the Recycle Bin to view and restore dropped database objects.
1. Click the SQL Workshop icon on the Workspace home page.
2. Click Utilities.
3. Click Recycle Bin.
4. Click View Objects.
5. To filter the report, select an object type, enter the object name in the Original
Name field, and click Go.
6. Click the View icon to access the Object Detail page. On this page you can:
7. To view object details, click the View icon adjacent to the original name.
On the Object Details page, you can:
· Click Restore Object to restore the current object
· Click Purge to permanently delete the current object
Emptying the Recycle Bin:
To empty the Recycle Bin without viewing the objects:
1. Click the SQL Workshop icon on the Workspace home page.
2. Click Utilities.
3. Click Recycle Bin.
4. From the Tasks list on the right side of the page, select Purge Recycle Bin.
1.10.2 Enterprise Manager
To perform the Flashback Drop operations1. In the Backup/Recovery section of the Maintenance page, select Perform Recovery.
The Perform Recovery page appears.
2.In the Object Level Recovery section, select Tables for the object type. The page reloads with options appropriate for tables in the Object Level Recovery section. For the Operation Type, select Flashback dropped tables and click Perform Object Level Recovery.
The Perform Object Level Recovery: Dropped Objects Selection page appears.
3.The Search form lets you search among the dropped objects in the Recycle Bin for the objects you want to recover. Provide values for one or both of the Schema Name and Table fields, and click Go to search.
When the page refreshes, the Results section lists the objects matching your search. If you only see the Recycle Bin listed, then click the arrow next to the Recycle Bin to expand its contents by one level, showing dropped tables matching your search but not their dependent objects. You can further expand individual tables, or click Expand All to see all objects in the Recycle Bin, including both dropped tables and dependent objects such as indexes and triggers. For each table listed, you can click View Content in the Operation column to display its contents.To select one or more tables for Flashback Drop, click the checkbox next to each table.
When you have selected all of the objects to restore, click Next.
The Perform Object Level Recovery: Rename page appears.
4.If needed, specify new names for any dropped objects you are returning to your database. The primary reason for renaming objects when you retrieve them from the recycle bin is if you have created new tables with the same names as tables being retrieved. If you need to rename some objects, then enter new names as needed in the New Name field in the list of tables being flashed back. Click Next to continue.
The Perform Object Level Recovery: Review page appears. This page displays an impact analysis, showing the full set of objects to be flashed back, including the dependent objects, as well as the names they will have when the Flashback Drop operation is complete.
5.If you are satisfied with the changes listed in the review, click Submit to perform the Flashback Drop.
A confirmation page should indicate the success of the operation.
6.Click OK to return to the database home page.
