In the Oracle 10G database, a database object of a Recy Bin is introduced.
Recycle bin, in principle is a data dictionary table, place the database object information that users DROP. The object of the user performs DROP operation is not deleted by the database, and it will still take up space. Unless it is handled by the user or because the storage space is not enough. The database has such a function to reduce many unnecessary troubles. Often see the developer mistakenly delete the table, and hurriedly finds the situation of DBA to find ways. I believe that with 10G's wide range of applications, this situation should be relatively rare, we can make full use of 10G flashback (flashback, flashback, flashing) function to avoid a lot of artificial misoperation.
The relevant information on DBA management can be obtained from user_recyclebin (DBA_RECYCLEBIN).
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;
Connected.
SQL> SELECT TABLE_NAME from User_Tables;
No rows selected
SQL>
SQL> Desc user_recyclebin
Name NULL? TYPE
------------------------------------- ------------------
Object_name not null varchar2 (30)
ORIGINAL_NAME VARCHAR2 (32)
Operation varchar2 (9)
TYPE VARCHAR2 (25)
TS_NAME VARCHAR2 (30)
Createtime varchar2 (19)
DROPTIME VARCHAR2 (19)
DROPSCN NUMBER
Partition_name varchar2 (32)
Can_undrop varchar2 (3)
CAN_PURGE VARCHAR2 (3)
Related Not Null Number
Base_Object NOT NULL NUMBER
PURGE_Object Not Null Number
Space Number
SQL>
User_Recyclebin is mostly self-explanatory, relatively easy to understand.
SQL> SELECT Object_name from user_recyclebin;
No Rows SELECTEDSQL>
SQL> SELECT Object_name from user_recyclebin;
No rows selected
SQL> CREATE TABLE FOO AS SELECT * from DUAL;
Table created.
SQL> SELECT TABLE_NAME from User_Tables;
Table_name
----------------------------------------------
Foo
SQL> DROP TABLE FOO;
Table Dropped.
SQL> SELECT TABLE_NAME from User_Tables;
No rows selected
SQL> show recyclebin
Original Name Recyclebin Name Object Type Drop Time
------------------------------------------------ -------- -------------------
Foo bin $ v3f / oyuitrcef2cots5jaa == $ 0 TABLE 2004-10-30: 14: 37: 39
SQL>
SQL * Plus command show recyclebin is equivalent to this SQL:
SQL> Select Original_Name, Object_name, Type, DROPTIME AER_RECYCLEBIN
Original_name Object_name Type Droptime
-------------------------------------------- ----- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Foo bin $ v3f / oyuitrcef2cots5jaa == $ 0 TABLE 2004-10-30: 14: 37: 39
SQL>
SQL> DESC "bin $ v3f / oyuitrcef2cots5jaa == $ 0"
Name NULL? TYPE
------------------------------------- ---------------
Dummy varchar2 (1)
SQL>
When a table is deleted and moved to "Recycle Bin", its name is to be converted. Such an object is obvious to avoid repetition of the same object name. (Different from the recycle bin of the Windows operating system, the recycle bin in Windows has been special processing, and the operating system file can be renamed.)
The translated name format is as follows:
BIN $ UNIQUE_ID $ VERSION
Where bin represents Recyclebin Unique_ID is the unique sign of the object in the database, 26 character length Version indicates the version number of the object.
Note: In the 10G beta version, the 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.
Below we verify that the uniqueness of the database object name in the recycle station:
SQL> CREATE TABLE FOO AS SELECT * from Dual; Table Created.
SQL> SELECT TABLE_NAME from User_Tables;
Table_name
-------------------------------------------------- ------------
Foo
SQL> DROP TABLE FOO;
Table Dropped.
SQL> show recyclebin
Original Name Recyclebin Name Object Type Drop Time
------------------------------------------------ -------- -------------------
Foo bin $ VL ZSQVLQF6R2NYNWAQTVW == $ 0 TABLE 2004-10-30: 15: 01: 42
Foo bin $ v3f / oyuitrcef2cots5jaa == $ 0 TABLE 2004-10-30: 14: 37: 39
SQL>
It can be seen, although the source table name is the same, the object name in the recycling station is different.
Operation for Recycle bin objects
The table that has already been placed in the recycling station cannot be deleted with the drop command (note the double quotes on the object name):
SQL> Drop Table "BIN $ V3F / OYUITRCEF2COTS5JAA == $ 0"
2 /
Drop Table "BIN $ V3F / OYUITRCEF2COTS5JAA == $ 0"
*
Error At Line 1:
ORA-38301: Can Not Perform DDL / DML over Objects in Recycle Bin
SQL>
If you want to clear the object, use the purge command:
SQL> Purge Table "BIN $ V3F / OYUITRCEF2COTS5JAA == $ 0"
2 /
Table purged.
SQL> Select Object_name, Original_name from User_Recyclebin
Object_name Original_name
-------------------------------------------
BIN $ VL ZSQVLQF6R2NYNWAQTVW == $ 0 foo
SQL>
The object is already clear. If you directly empty the objects in all Recycle bin:
SQL> purge recyclebin;
Recyclebin purged.
SQL> Select Object_name, Original_name from User_Recyclebin
No rows selected
SQL>
Restore the table, use back the function of the flash:
SQL> Flashback Table Foo to Before DROP;
Flashback Complete.
SQL> SELECT TABLE_NAME from User_Tables;
Table_name
-------------------------------------------------- ------------
Foo
SQL>
Note: In the 10G beta version, this is used to use the undrop command to do this.
Sometimes, the table that may be the same name is deleted into the recycle bin: SQL> Drop Table foo;
Table Dropped.
SQL> show recyclebin
Original Name Recyclebin Name Object Type Drop Time
------------------------------------------------ -------- -------------------
Foo bin $ lftbzojisxaw8u0bio7pna == $ 0 TABLE 2004-10-30: 15: 18: 03
SQL> CREATE TABLE FOO AS SELECT * from DUAL;
Table created.
SQL> DROP TABLE FOO;
Table Dropped.
SQL> show recyclebin
Original Name Recyclebin Name Object Type Drop Time
------------------------------------------------ -------- -------------------
Foo bin $ J63QauakTmc1glat Imjeg == $ 0 TABLE 2004-10-30: 15: 18: 50
Foo bin $ lftbzojisxaw8u0bio7pna == $ 0 TABLE 2004-10-30: 15: 18: 03
SQL> Flashback Table Foo to Before DROP;
Flashback Complete.
SQL> show recyclebin
Original Name Recyclebin Name Object Type Drop Time
------------------------------------------------ -------- -------------------
Foo bin $ lftbzojisxaw8u0bio7pna == $ 0 TABLE 2004-10-30: 15: 18: 03
SQL>
The default recovery is the first FOO table that is deleted. If you want to recover the specified table, you can specify your other name after the Flashback Table will be added later:
SQL> Flashback Table "bin $ lftbzojisxaw8u0bio7pna == $ 0" to Before DROP;
Flashback table "bin $ lftbzojisxaw8u0bio7pna == $ 0" to Before Drop
*
Error At Line 1:
ORA-38312: Original Name is buy by an existing object
SQL> Flashback Table "bin $ lftbzojisxaw8u0bio7pna == $ 0" to Before Drop
2 rename to foo2;
Flashback Complete.
SQL>
