Oracle Recycle Bin Mechanism Introduction

Recycle Bin Concept

Starting with Oracle 10g, a concept called the Recycle Bin (Recycle bin) was introduced. Its full name is called Tablespace Recycle Bin. The Recycle Bin is actually a logical container (logical region), which is somewhat similar to the Recycle Bin of the window system. It is based on existing allocated space in the tablespace, rather than physically delineating a fixed area from the tablespace as the Recycle Bin. This means that objects in the Recycle Bin and tablespace share a storage area, and the system does not reserve space for the Recycle Bin. Therefore, when the table is dropped, if the available space is sufficient and the Recycle Bin is not cleaned up, the dropped objects will always be in the Recycle Bin, but if free space is tight, the database overwrites the objects in the Recycle Bin in FIFO order. So the Recycle Bin mechanism is not a hundred-percent insurance mechanism. In addition, the principle is that it is a data dictionary table that places the user's dropped database object information. The object that the user makes a drop operation is not actually 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. When the user, developer, or even DBA has deleted the table by mistake, we do not have to restore the entire database or table space, directly using the Oracle 10g Flashback (FLASHBACK, Flashback) function to restore the deleted table. So we can avoid a lot of manual misoperation. This is a very useful feature for DBAs.

Recycle Bin function

Recycle Bin The main benefit of this feature is that when a table is mistakenly deleted there is a recovery mechanism that does not have to be implemented through database restore. Avoid a large number of manual operation errors. As well as complex operations such as database restores. Make the database management, maintenance more simple and convenient. In the case of a SQL Server database, you must restore the entire database to find the dropped tables. The visible Recycle Bin function is indeed a groundbreaking feature.

Managing the Recycle Bin

Turn the Recycle Bin on and off

First you can check whether the database has the Recycle Bin mechanism enabled by the command, as shown below value= on indicates that the Recycle Bin mechanism is turned on. Off indicates that the Recycle Bin mechanism is closed.

sql> SHOW PARAMETER RecycleBin;

NAME              TYPE        VALUE

--------     ----------- ----------

RecycleBin      string         on

Or

Sql> SELECT NAME, VALUE from V$parameter WHERE name= ' RecycleBin ';

NAME                      VALUE

----------------- --------------------

RecycleBin on                  

You can enable or disable the Recycle Bin feature by setting the initialization parameters RecycleBin. Of course, you can also close the Recycle Bin with a command

sql> ALTER SYSTEM SET Recyclebin=off;

System altered.

sql> ALTER SESSION SET Recyclebin=off;

Session altered.

sql> SHOW PARAMETER RecycleBin;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

RecycleBin                           string      OFF

You can open the Recycle Bin with a command

sql> ALTER SYSTEM SET recyclebin=on;

System altered.

sql> ALTER SESSION SET recyclebin =on;

Session altered.

sql> SHOW PARAMETER RecycleBin;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

RecycleBin                           string      on

View Recycle Bin Objects

Let's take a look at an example of how we can see the drop table object in the Recycle Bin if we accidentally drop the table test by mistake.

Sql> Show User

USER is "ODS"

Sql> CREATE TABLE Test (name VARCHAR2 (16));

Table created.

sql> INSERT INTO Test select ' Kerry ' from dual;

1 row created.

sql> INSERT INTO Test select ' Ken ' from dual;

1 row created.

Sql> commit;

Commit complete.

sql> drop table test;

Table dropped.

SQL 1:

Sql> Show RecycleBin

ORIGINAL name    recyclebin name                OBJECT TYPE  DROP time

---------------- ------------------------------ ------------ -------------------

TEST             bin$blmi9vltn3tgukjagyxoia==$0 TABLE        2014-10-06:11:25:38

SQL 2: where RecycleBin is synonymous with user_recyclebin.

COL object_name for A30

COL Original_name for A8

COL Operation for A9

COL TYPE for A8

COL Droptime for A19

COL Ts_name for A30

          , Original_name

          , Operation

          , TYPE

          , Droptime

          , Ts_name

From RecycleBin;

Or

--View the Recycle Bin object for the current user of the database

Sql> SELECT * from RecycleBin

SQL 3:

-- View the Recycle Bin object for the current user of the database

Sql> SELECT * from User_recyclebin;

SQL 4: requires permission to query.

-- View all objects in the database recycle bin

Sql> SELECT * from Dba_recyclebin;

To avoid the duplication of deleted tables and the names of similar objects, the deleted table and the dependent objects are placed in the Recycle Bin, and the Oracle database renames the deleted object names, such as the table test table

Sql> Show RecycleBin

ORIGINAL name    recyclebin name                OBJECT TYPE  DROP time

---------------- ------------------------------ ------------ -------------------

TEST             bin$blmi9vltn3tgukjagyxoia==$0 TABLE        2014-10-06:11:25:38

We created the table test and then deleted the table test, as shown below, although the original_name is consistent, but RecycleBin name is different.

Sql> CREATE TABLE Test (name VARCHAR2 (16));

Table created.

sql> drop table test;

Table dropped.

Sql> Show RecycleBin

ORIGINAL name    recyclebin name                OBJECT TYPE  DROP time

---------------- ------------------------------ ------------ -------------------

TEST             bin$blmi9vlun3tgukjagyxoia==$0 TABLE        2014-10-06:14:40:52

TEST             bin$blmi9vltn3tgukjagyxoia==$0 TABLE        2014-10-06:11:25:38

The naming convention for RecycleBin name is bin$guid$version where the GUID is Globaluid, which is a globally unique, 24-character identity object, which is an identity used internally by Oracle. Where $version is the version number assigned by the Oracle database.

Restore Recycle Bin Objects

Restoring objects such as tables, indexes, etc. deleted by the Recycle Bin is implemented by flashback drop. as shown below.

Sql> FLASHBACK TABLE TEST to before DROP;

Flashback complete.

Sql> SELECT * from TEST;

NAME

----------------

Kerry

Ken

But if the above two test tables are deleted, then the flashback drop is a bit of a point.

Sql> Show RecycleBin

ORIGINAL name    recyclebin name                OBJECT TYPE  DROP time

---------------- ------------------------------ ------------ -------------------

TEST             bin$blmi9vlxn3tgukjagyxoia==$0 TABLE        2014-10-06:15:10:25

TEST             bin$blmi9vlwn3tgukjagyxoia==$0 TABLE        2014-10-06:15:10:09

Sql> Flashback table Test to before drop;

Flashback complete.

Sql> Show RecycleBin

ORIGINAL name    recyclebin name                OBJECT TYPE  DROP time

---------------- ------------------------------ ------------ -------------------

TEST             bin$blmi9vlwn3tgukjagyxoia==$0 TABLE        2014-10-06:15:10:09

Sql> select * from test;

No rows selected

As shown above, if the two table test with the same name is deleted, then the drop table test is flashed back to the last deleted table (LIFO principle), and if you continue the flashback operation, the ORA-38312 error will be reported.

Sql> Flashback table Test to before drop;

Flashback table test to before drop

*

ERROR at line 1:

Ora-38312:original name is used by an existing object

You can then rename the table name in the flashback process to resolve the issue:

Sql> Flashback table Test to before drop rename to test_2;

Flashback complete.

Sql> select * from Test_2;

NAME

----------------

Kerry

Ken

Also, if the Recycle Bin has two dropped table test, what if you want to flash back the first deleted table?

Sql> Show RecycleBin

ORIGINAL name    recyclebin name                OBJECT TYPE  DROP time

---------------- ------------------------------ ------------ -------------------

TEST             bin$blmi9vlxn3tgukjagyxoia==$0 TABLE        2014-10-06:15:10:25

TEST             bin$blmi9vlwn3tgukjagyxoia==$0 TABLE        2014-10-06:15:10:09

In fact, this is also very good processing, directly specify RecycleBin name to flash back.

Sql> Flashback table "Bin$blmi9vlwn3tgukjagyxoia==$0" to before drop;

Emptying the Recycle Bin

After the database object is deleted, the database renames it to the object beginning with bin$, and you can view its original object name by Original_name. Remember, placing a table in the Recycle Bin does not free up space in the original table space. If you want to completely delete the table without putting the table in the Recycle Bin, you can permanently delete the table by using the following command. Of course, you won't be able to flash back to the table by using the Flashback feature.

DROP TABLE table_name PURGE;

If the table is dropped in the database when it is put into the Recycle Bin, so that the occupied space is not freed, then when the free space is not enough, the deleted table will also occupy the storage space?

The answer is simple: when the tablespace is fully occupied by the Recycle Bin data, so that the data file must be expanded to accommodate more data, you can say that the tablespace is under "spatial pressure." At this point, the object is automatically cleared from the Recycle Bin in FIFO mode. Related objects, such as indexes, are deleted before the table is dropped.

Similarly, the spatial pressure may be caused by a user limit defined by a particular table space. The tablespace may have enough free space, but the user may have exhausted the portion of the table space that is allocated to it. In this case, Oracle automatically clears the object that belongs to that user in the tablespace.

In addition, there are several ways to manually control the Recycle Bin. If you need to purge a specific table named TEST after it has been removed from the Recycle Bin, you can perform

PURGE TABLE table_name;

Or use the name in its Recycle Bin:

PURGE TABLE "bin$04lhcpndanfgmaaaaaanpw==$0";

This command saves space by removing the table TEST and all related objects, such as indexes, constraints, and so on, from the Recycle Bin. However, if you want to permanently delete an index from the Recycle Bin, you can use the following command to do the work:

PURGE INDEX In_test1_o1;

This command will simply drop the index and leave a copy of the table in the Recycle Bin. Sometimes cleaning at a higher level may be useful. For example, you may want to clear all objects in the Recycle Bin of the table space USERS. Can perform:

PURGE tablespace USERS;

You may want to empty the Recycle Bin only for a specific user in that tablespace. This approach may be useful in environments where the data warehouse type creates and deletes many temporary tables. You can change the above command to only clear specific users:

PURGE tablespace USERS USER SCOTT;

To free up space occupied by the entire Recycle Bin, you need to empty the Recycle Bin with the following command:

PURGE RecycleBin;

Remember purge RecycleBin just clears the object from the current user Recycle Bin, the object under Dba_recyclebin is not deleted, and if you want to clear the object of the current database Recycle Bin, you must use the following command (DBA authority)

PURGE Dba_recyclebin

Flashback Drop Precautions

1: Only for non-system tablespaces and locally managed tablespaces.

As shown below, in the system table space, the Table object is deleted from the system and is not stored in the Recycle Bin.

Sql> Show User

USER is "SYS"

Sql> CREATE TABLE Test (name VARCHAR2 (12));

Table created.

sql> drop table test;

Table dropped.

Sql> show RecycleBin;

2: The object's reference constraint is not restored, and the foreign key constraint to the object needs to be rebuilt.

3: The object can be restored successfully, depending on whether the object space is overwritten and reused.

4: When you delete a table, materialized views that depend on the table are also deleted, but because materialized views are not put into recycle Binzhong, when you execute flashback drop,

It is not possible to restore materialized views that depend on them. Requires a DBA to rebuild manually.

5: Only queries are supported for objects in the Recycle Bin (Recycle bin). No other operations such as DML, DDL, etc. are supported.

Resources:

http://blog.csdn.net/tianlesoftware/article/details/4677378

Oracle Recycle Bin Mechanism Introduction