Oracle
"Recycle Bin" in Oracle 10G
by fenng
Http://www.DBAnotes.net
In Oracle 10G, the concept of a Recycle Bin (recycle Bin) is introduced.
The Recycle Bin, in principle, is a data dictionary table, where the user drops the database object information. The object that the user is dropping is not deleted by the database. will still occupy space. Unless the user is manually purge or the database is cleared because of insufficient storage space. The database has this function, can reduce a lot of unnecessary trouble. Often see developers mistakenly delete the table, hurriedly looking for the DBA to find a way, believe that, with the wide range of 10G applications , this situation should be relatively rare.
Information on DBA administration can be obtained from User_recyclebin (Dba_recyclebin). From the current beta version, the manual says the show Recycle Bin command is not available through Sql*plus.
To facilitate testing, we created a tablespace foo, size 1M, created a user foo, and the default tablespace is foo.
----------------------------------------------------
Sql> CONNECT Foo/foo
is connected.
Sql> SELECT table_name from User_tables;
No rows selected
Sql> DESC User_recyclebin
is the name empty? Type
----------------------------------------- -------- ----------------
......
......
Most of the user_recyclebin are self explanatory, and their meanings are relatively easy to understand.
Sql> SELECT object_name from User_recyclebin;
No rows selected
sql> CREATE TABLE foo as SELECT * from DUAL;
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
is the name empty? Type
----------------------------------------- -------- -------------
DUMMY VARCHAR2 (1)
When a table is deleted and moved to the Recycle Bin, its name is converted. Such a goal is obvious in order to avoid duplication of names of similar objects. (on Windows, after special processing, the operating system files in the Recycle Bin can be duplicate.)
The converted name format is as follows:
rb$ $objn $object_type$version
The RB, which represents recycle Bin. OBJN is the directory object number for the table. Object_type represents an object type. Version represents the revision number.
specified by the database. After the Foo table was deleted, it became rb$$41888$table$0 in the database Recycle Bin. As you can see from the name, the type of the object is Table,version is 0.
sql> CREATE TABLE foo as SELECT * from DUAL;
Table has been created.
Sql> SELECT table_name from User_tables;
table_name
------------------------------
Foo
Rb$$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 FOO
Rb$$41888$table$0 FOO
Sql>
Can see, although the source table name is the same, the object name in the Recycle Bin is different.
Tables that have been placed in the Recycle Bin cannot be deleted with the drop command:
sql> drop table rb$$41888$table$0;
drop table Rb$$41888$table$0
*
ERROR is on line 1th:
Ora-38301:can not perform ddl/dml over objects in recycle Bin
If you want to clear the change object, 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 has been cleared away.
Restore the table using the Undrop command.
sql> undrop table rb$$41889$table$0;
Table undropped.
Sql> SELECT object_name, original_name from User_recyclebin;
No rows selected
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 restored table's name is rb$$41889$table$0, and if you specify a name, use "as Table name"
such as: Undrop table rb$$41889$table$0 as foo such statements. For more detailed syntax, consult the 10G SQL Reference manual.
Sql>
sql> desc rb$$41889$table$0;
is the name empty? 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 at this time, we can observe the change of the table version number in the Recycle Bin.
If the space is sufficient, the objects in the Recycle Bin will remain (if you do not purge by hand). If there is not enough space, the objects in the Recycle Bin will be emptied. Let's look at the following example:
Let's create a larger table:
Sql> select object_name from User_recyclebin;
object_name
------------------------------
Rb$$41893$table$0
Sql> CREATE TABLE Foo_bigger as SELECT * from All_objects WHERE rownum<7980;
Table has been created.
Sql> SELECT table_name from User_tables;
table_name
------------------------------
Foo_bigger
Sql> SELECT object_name, original_name from User_recyclebin;
No rows selected
Sql>
Oh, indeed, the contents of the Recycle Bin have been deleted. Also note that in the lack of space when the drop table, also can not be put into the Recycle Bin.
Reference Documentation:
Oracle10i Database Administrator ' s Guide Release 1 (10.1)-BETA
Part No. B10739-01
oracle10g Undrop function Little Practice by Overmars
Original source:
<a href= "http://www.dbanotes.net/Oracle/Oracle-10G-RecycleBin.htm" >http://www.dbanotes.net/oracle/ Oracle-10g-recyclebin.htm</a>
