Oracle RecycleBin Detailed

Today in the SOA application database using the Dbms_redefition package for the online non-partitioned table conversion partition table operation, I would like to drop off the temporary table cube_scope_temp accidentally forgot to add "temp" directly executed, I am aware of the problem is too late , because of project construction and other legacy issues, the database is non-archival mode, and did not do backup, because of fear of affecting the business, a moment did not think of Oracle Recycle Bin RecycleBin This method, directly the backup of the table statement to create a table, to ensure the normal development of business, is suffering how to recover data, Suddenly want Oracle 10g to provide the Recycle Bin this feature, the successful implementation of data recovery!
Oracle Recycle Bin RecycleBin is a new feature of 10g, when we drop table Cube_scope "purge", if you do not specify purge, the system simply renames the table to the beginning of the name bin$, and in the data dictionary modified the relevant data, The physical space occupied by the table is not really recycled, the space occupied by this time is the original table space, when the table space is not enough, Oracle will follow the dropscn# automatically to clean up the Recycle Bin in the space occupied by the image, 10g default is to turn on the Recycle Bin function.
First, how to check whether to turn on the Recycle Bin function?
Sql> Show Parameter RecycleBin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
RecycleBin string on
On: Indicates the Recycle Bin function enabled by the tablespace, it is recommended that all data be turned on this function, no harm!
Note: This parameter can be set to session level Open, or set to the system level, without restarting to take effect
Second, how to delete and free the occupied space without passing through the Recycle Bin?
sql> drop table Cube_scope Purge
Note: This command is equivalent to Truncate+drop operation, generally do not recommend this operation!
Thirdly, how to restore the RecycleBin in the Recycle Bin?
Sql> Flashback table Cube_scope to before drop
The table name can be the dba_recyclebin.object_name of the Recycle Bin system or it can be dba_recyclebin.original_name
But at this point, I have a backup of the DDL statement rebuilt a new table, this time again with this command to restore the obvious error, this time what to do, can only be reverted to an alias, the specific Operation command is
Sql> Flashback table Cube_scope to before drop rename to Cube_scope_old
Now that you have recovered the data from the table before the deletion, you can only insert Cube_scope from the data in the Cube_scope_old
sql> INSERT INTO Cube_scope select * from Cube_scope_old t
Successfully recovered the data, is it possible to call it a call? No, what else have you forgotten to do? Think about it?
Note: If you drop the table, the index is dropped and the table is returned in this way, but what about your index? Where's your restraint? After the table is restored, be sure to set up the index rebuild on the table (remember), the index is lost most of the performance, the constraints can cause business data confusion (must be noted)
Iv. How to manually remove the image from the Recycle Bin?
Sql> Purge Table Orabpel.cube_scope_old--Clear the specific image
Note: If the DBA user is working on other user data at this time, you should add the user name when clearing the table in the Recycle Bin, otherwise the report is not in the Recycle Bin
Sql> purge tablespace Orapel--Clears the specified table space pair like
Sql> purge tablespace orapel user Orabpel--delete all the images under the specified user for the tablespace
Sql> Purge RecycleBin--emptying the entire Recycle Bin
V. Show RecycleBin why is there no data?
First of all, we need to understand that RecycleBin is synonymous with user_recyclebin, so your current login user is the system at this time using
Show RecycleBin has no data on it
Six, if the same pair like multiple deletion how to identify in the RecycleBin?
In Dba_recyclebin, each deletion of an image is named with bin$, and there are corresponding DROPSCN, createtime, and droptime to locate and recover from these pairs of images.
VII. Principles of Oracle Space Utilization
1. Unused space using an existing table space
2. If there is no free space, check the Recycle Bin, for the object of the Recycle Bin according to the principle of FIFO, for the first deleted objects,
Oracle is first removed from the Recycle Bin when it is out of space to meet the demand for new allocated space
3. If the Recycle Bin also has no objects to clean up, check that the tablespace is self-expanding, expand the tablespace if self-expanding, and then assign a new empty
Room
4. If the table space is not self-expanding, or has not been self-expanding (reaching the maximum limit), the direct report space is insufficient error, the program terminates
Eight, the drop off of the image is not going to go through the Recycle Bin?
The following types of drop do not place related pairs into the Recycle Bin RecycleBin
* Drop tablespace: Clears all the recyclebin in the tablespace that belong to the
* Drop User: All RecycleBin that belong to the user will be cleared
* Drop cluster: All members belonging to the cluster in the RecycleBin will be cleared as
* Drop type: All dependent on the type in RecycleBin will be cleared
Also need to pay attention to a situation, like the table space to have enough space, or even drop off through recyclebin due to the lack of space Oracle will automatically delete Oh (remember)!

Oracle RecycleBin Detailed