"Recycle Bin" in Oracle 10G

In the Oracle 10g database, a database object with a Recycle Bin (Recycle bin) was introduced.

The Recycle Bin, in principle, is a data dictionary table that places the user's dropped database object information. Objects that are dropped by the user are not deleted by the database and will still occupy space. Unless the user is manually purge or because the storage space is not enough to be erased by the database. Database has such a function, can reduce a lot of unnecessary trouble. It's common to see developers mistakenly deleting tables and rushing to find a DBA to figure it out. Believe that with the wide range of 10G applications, this situation should be relatively rare, we can make full use of the 10g flash back (FLASHBACK, flashback, flashback?) function to avoid a large number of manual misoperation.

Information about DBA administration can be obtained from User_recyclebin (Dba_recyclebin).

To facilitate testing, we created a table space foo, which is 1M in size, created a user foo, and 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 (ORIGINAL) _name VARCHAR2 (+) operation VARCHAR2 (9) TYPE VARCHAR2 (+) Ts_name VARCHAR2 (+) Createtime VARCHAR2 (+) Droptime VARCHAR2 (+) DROPSCN number Partition_name VARCHAR2 (+) 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 are mostly self-explanatory and relatively easy to understand.

Sql> SELECT object_name from User_recyclebin; No rows selected sql> 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 >

The Sql*plus command show RecycleBin is equivalent to this SQL:

Sql> SELECT original_name, object_name, TYPE, droptime from User_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 the Recycle Bin, its name is converted. This is clearly intended to avoid duplication of the names of similar objects. (unlike the Windows operating system's Recycle Bin, the Recycle Bin in Windows has been specially processed and the operating system files can have duplicate names.) )

The converted name format is as follows:

Bin$unique_id$version

• where bin represents RecycleBin
• UNIQUE_ID is a unique flag for this object in the database, 26 characters long
• The version number that represents the object

Note: In the 10g beta version, the name format is as follows:

rb$ $objn $object_type$version

One of the RB, representing recycle Bin. OBJN is the directory object number for the table. Object_type represents the object type. Version indicates the revision number. specified by the database.

Let's verify the uniqueness of the database object name in the Recycle Bin:

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>

Can be seen, although the source table name is the same, the object name in the Recycle Bin is different.

Operations on Recycle Bin objects

Tables that have been placed in the Recycle Bin cannot be deleted with the drop command (note the double quotation marks 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 to 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 has been cleared out. If you empty all the objects in the Recycle Bin directly:

Sql> PURGE RecycleBin; RecycleBin purged. Sql> SELECT object_name, original_name from User_recyclebin; No rows selected sql>

Recovery table, with the function of the flashback table:

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 release, use the Undrop command to do this.

Sometimes, a table of the same name may be deleted to 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 to be deleted. If you want to restore the specified table, you can specify a different name after flashback table with the specified RecycleBin parameter:

sql> FLASHBACK TABLE "bin$lftbzojisxaw8u0bio7pna==$0" to before DROP; FLASHBACK TABLE "Bin$lftbzojisxaw8u0bio7pna==$0" to before DROP * ERROR @ line 1:ora-38312:original name was used by an Existing object sql> FLASHBACK TABLE "bin$lftbzojisxaw8u0bio7pna==$0" to before DROP 2 RENAME to Foo2; Flashback complete. Sql>

"Recycle Bin" in Oracle 10G