"Recycle bin" in Oracle 10G"

In principle, the recycle bin is a data dictionary table that stores information about database objects dropped by users. the Drop object is not deleted by the database and will still occupy space. unless the user Purge manually or the database is cleared because the storage space is insufficient. the database has such a function, which can reduce a lot of unnecessary troubles. developers often mistakenly Delete the table and rush to ask the DBA for a solution. I believe that with the large-scale application of 10 Gb, this situation should be rare. DBA management information can be obtained from USER_recyclebin (DBA_recyclebin. from the current Beta version, the show recycle bin command of SQL * Plus mentioned in the manual is still unavailable. to facilitate testing, we created a tablespace Foo with a size of 1 MB and a user Foo with the default tablespace Foo.
----------------------------------------------------
SQL>; CONNECT foo/foo
Connected.
SQL>; SELECT table_name FROM user_tables;
Unselected row
SQL>; DESC user_recyclebin
Is the name empty? Type
-----------------------------------------------------------------
......
......
User_recyclebin is mostly self-explanatory and easy to understand.
SQL>; SELECT object_name FROM user_recyclebin;
Unselected row
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 is 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 must be converted to avoid
Duplicate names of similar objects. (In Windows, operating system files in the recycle bin can be renamed .)
The converted name format is as follows:
RB $ objn $ object_type $ version
Here, RB indicates Recycle Bin. objn is the directory object number of the table. object_type indicates the object type. version indicates the version number.
Specified by the database. After the foo TABLE is deleted, it becomes RB $41888 $ TABLE $0 in the database recycle bin. We can see from the name that the object's
The type is Table, and the 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
------------------------------
FOO
Rb$ $41888 $ TABLE $0
SQL>; DROP TABLE foo;
The table is 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>;
Although the source table name is the same, the object names in the recycle bin are different.
Tables that have been placed in the recycle bin cannot be deleted using the drop command:
SQL>; drop table RB $41888 $ TABLE $0;
Drop table rb$ $41888 $ TABLE $0
*
ERROR is located in row 1st:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
To clear the modified object, run 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.
Restore the table by running the Undrop command.
SQL>; undrop table RB $41889 $ TABLE $0;
Table undropped.
SQL>; SELECT object_name, original_name FROM user_recyclebin;
Unselected row
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 name is RB $41889 $ TABLE $0. If you specify a name, use "as TABLE name"
For example, undrop table RB $41889 $ TABLE $0 as Foo. For more detailed syntax, see
See 10 Gb 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 is 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 changes to the TABLE version number in the recycle bin.
If the space remains sufficient, the objects in the recycle bin will remain unchanged if you do not perform Purge manually ). If the space is insufficient,
Objects in the recycle bin are cleared. Let's look at the example below:
Let's create a bigger 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;
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 row
SQL>;
Oh, actually, the content in the recycle bin has been deleted. Note that when the space is insufficient, Drop the table and you cannot put it in the recycle bin.