"Recycle Bin" in Oracle 10g by fenng http://www.dbanotes.net
In Oracle 10G, a concept of recycle bin is introduced.
Recycle bin, in principle, it is a data dictionary table, place the database object information that users DROP. The user performs DROP operations and is not deleted by the database, and it will take up space. Unless it is handled by the user or because of the user Not enough by the database. The database has such a function to reduce a lot of unnecessary troubles. I often see that the developers have fallen to delete the table, hurriedly find the situation of DBA to find ways, believe, with 10g Scope applications, this situation should be relatively few.
DBA management information can be obtained from user_recyclebin. From now on the Beta version, the manual is not available through the SQL * Plus's show recycle bin command.
For ease of testing, we created a table space FOO, a size of 1m, creating a user foo, the default table space is Foo.
-------------------------------------------------- -
SQL> Connect foo / foo is connected.
SQL> SELECT TABLE_NAME from User_Tables;
Unselected
SQL> DESC User_recyclebin name is empty? Type -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
..........
User_Recyclebin is mostly self-explanatory, and the meaning is relatively easy to understand.
SQL> SELECT Object_name from user_recyclebin;
Unselected
SQL> CREATE TABLE FOO AS SELECT * from DUAL;
The table has been created.
SQL> SELECT TABLE_NAME from User_Tables;
Table_name ------------------------------ Foo
SQL> DROP TABLE FOO;
The table has been discarded.
SQL> SELECT TABLE_NAME from User_Tables;
Table_name ------------------------------ RB $$ 41888 $ TABLE $ 0
SQL>
SQL> Select Object_name, Original_name from User_Recyclebin
Object_name Original_name -------------------------------------- RB $$ 41888 $ TABLE $ 0 FOO
SQL> DESC RB $$ 41888 $ table $ 0 name is empty? Type ----------------------------------- -------------------------
Dummy varchar2 (1)
When a table is deleted and moved to "Recycle Bin", its name is to convert. This purpose is obvious to avoid repetition of the same object name. (Windows, after a special process, the operation in the recycle station System files can be renamed.)
The translated name format is as follows:
RB $$ Objn $ Object_type $ VERSION
The RB represents the directory object number of Recycle bin. Objn. Object_type represents an object type. Version represents the version number. Specified by the database. After the FOO table is deleted, it becomes RB $$ 41888 $ TABLE $ 0 in the database recycling station. As can be seen from the name, the type of this object is Table, and Version is 0.
SQL> CREATE TABLE FOO AS SELECT * from DUAL;
The table has been created.
SQL> SELECT TABLE_NAME from User_Tables; Table_Name ------------------------------ Foorb $$ 41888 $ TABLE $ 0
SQL> DROP TABLE FOO;
The table has been discarded.
SQL> Select Object_name, Original_name from User_Recyclebin
Object_name Original_Name -------------------------------------- RB $$ 41889 $ TABLE $ 0 FOORB $ $ 41888 $ TABLE $ 0 FOO
SQL>
It can be seen, although the source table name is the same, the object name in the recycling station is different.
The table that has already been placed in the recycling station cannot be deleted with the drop command:
SQL> Drop Table RB $$ 41888 $ TABLE $ 0; Drop Table RB $$ 41888 $ TABLE $ 0 * Error Located in Chapter 1: ORA-38301: Can Not Perform DDL / DML over Objects in Recycle Bin
If you want to clear the change, use the purge command:
SQL> Purge Table RB $$ 41888 $ TABLE $ 0;
Table purged.
SQL> Select Object_name, Original_name from User_Recyclebin
Object_name Original_name -------------------------------------- RB $$ 41889 $ TABLE $ 0 FOO
The object is already clear.
Recovery table and use the undrop command.
SQL> Undrop Table RB $$ 41889 $ TABLE $ 0;
Table undropped.
SQL> Select Object_name, Original_name from User_Recyclebin
Unselected
SQL> SELECT TABLE_NAME from User_Tables;
Table_name ------------------------------ RB $$ 41889 $ TABLE $ 0
SQL> Desc foo; error: ORA-04043: Object foo does not exist
The name of the restored table is RB $$ 41889 $ TABLE $ 0, if the name is specified, use the "AS Table Name" such as: Undrop Table RB $$ 41889 $ TABLE $ 0 as Foo This statement. More detailed grammar, please check 10g SQL Reference Manual.
SQL> SQL> DESC RB $$ 41889 $ TABLE $ 0; Is the name? Type ------------------------------------------------------------------------------------------------ -----------------------------
Dummy varchar2 (1)
SQL> Drop Table RB $$ 41889 $ TABLE $ 0;
The table has been discarded.
SQL> SELECT TABLE_NAME from User_Tables;
Table_name ------------------------------ RB $$ 41889 $ TABLE $ 1
SQL>
If we delete RB $ 41889 $ TABLE $ 0 this time, the change in the table version number in the recycle station can be observed.
If the space is already enough, the object in the recycling station will remain (if you don't manually carry out Purge). If space is insufficient, the object in the recycling station will be emptied. See us below:
Let us create a big table:
SQL> SELECT Object_name from user_recyclebin;
Object_name ------------------------------ RB $$ 41893 $ TABLE $ 0sql> Create Table Foo_Bigger As SELECT * FROM All_Objects Where Rownum < 7980;
The table has been created.
SQL> SELECT TABLE_NAME from User_Tables;
Table_name ---------------------------- foo_bigger
SQL> Select Object_name, Original_name from User_Recyclebin
Unselected
SQL>
Oh, fruit, the content in the recycling station has been deleted. Also note that the DROP table is not in the space in space.
Reference documentation:
Oracle10i Database Administrator's Guide Release 1 (10.1) - BetaPart No. B10739-01
Oracle10g's undrop features a little practice by overmars
Original source: http://www.dbanotes.net/oracle/oracle-10G-Recyclebin.htm
