Introduction to ORACLE recycle bin mechanism and oracle recycle bin Mechanism

Introduction to ORACLE recycle bin mechanism and oracle recycle bin Mechanism

Recycle Bin Concept

A Recycle Bin concept was introduced from ORACLE 10 Gb. Its full name is Tablespace Recycle Bin. The recycle bin is actually a logical container (logical region), which is similar to the recycle bin of the WINDOW system. It is based on the existing allocated space in the table space, rather than physically setting a fixed area from the tablespace as the recycle bin. This means that objects in the recycle bin and tablespace share the storage area, and the system does not reserve space for the recycle bin. Therefore, after a table is dropped, if the available space is sufficient and the recycle bin is not cleared, the DROP object will always exist in the recycle bin, however, if the available space is insufficient, the database will overwrite the objects in the Recycle Bin in the first-in-first-out order. Therefore, the recycle bin mechanism is not a insurance mechanism. In principle, it is also a data dictionary table that stores information about database objects dropped by users. Objects in the Drop operation are not actually deleted by the database and 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. When users, developers, or even DBAs mistakenly Delete tables, we do not have to restore the entire database or table space and use the 10 GB flash back of ORACLE (FLASHBACK, flash back) function to restore the deleted table. In this way, we can avoid a lot of manual misoperations. This is a very useful function for DBAs.

Recycle Bin Function

The main benefit of the recycle bin feature is that a recovery mechanism is provided when a table is accidentally deleted, which does not need to be implemented through database restoration. Avoid a large number of manual misoperations. And complex operations such as database restoration. It makes database management and maintenance easier and convenient. For an SQL SERVER database, you must restore the entire database to find the DROP table. It can be seen that the recycle bin feature is indeed a ground-breaking feature.

Manage Recycle Bin

Enable and disable the recycle bin

First, you can run the command to check whether the recycle bin mechanism is enabled for the database. VALUE = ON indicates that the recycle bin mechanism is enabled. OFF indicates that the recycle bin mechanism is disabled.

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 function by setting the initialization parameter recyclebin. Of course, you can also use commands to disable the recycle bin.

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 use commands to enable the recycle bin.

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, as shown below. If the test table is accidentally dropped, how can we view the DROP table object in the recycle bin?

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:RECYCLEBIN is a synonym for 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

SELECT OBJECT_NAME 

          ,ORIGINAL_NAME

          ,OPERATION

          ,TYPE

          ,DROPTIME

          ,TS_NAME

FROM RECYCLEBIN;

Or

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

SQL> SELECT * FROM RECYCLEBIN

SQL 3:

--View the recycle bin objects of the current user in the database

SQL> SELECT * FROM USER_RECYCLEBIN;

SQL 4:Related permissions are required for query.

--View all objects in the database Recycle Bin

SQL> SELECT * FROM DBA_RECYCLEBIN;

To avoid duplicate names of the deleted tables and similar objects, the deleted tables and their dependent objects are placed in the recycle bin, and the ORACLE database renames the names of the deleted objects, example: Table TEST

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 TEST table and deleted the TEST table, as shown below. Although ORIGINAL_NAME is consistent, the 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 rule for recyclebin name is BIN $ GUID $ Version. GUID is GlobalUID, which is a globally unique and 24-character long identifier object. It is used internally by ORACLE. $ Version is the version number allocated by the ORACLE database.

Restore the Recycle Bin object

Restore objects such as tables and indexes deleted by the recycle bin through Flashback Drop. As shown below.

SQL> FLASHBACK TABLE TEST TO BEFORE DROP;

Flashback complete.

SQL> SELECT * FROM TEST;

NAME

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

kerry

ken

However, if the above two TEST tables are deleted, the Flashback Drop will be a bit interesting.

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 two tables with the same name "TEST" are deleted, the TEST table to be dropped is flashed back to the last table to be deleted (the principle of "going forward and going out" after ), if you continue flashing back at this time, a 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

In this case, you can rename the table name during the flash back process to solve the problem ..

SQL> flashback table test to before drop rename to test_2;

Flashback complete.

SQL> select * from test_2;

NAME

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

kerry

ken

In addition, if there are two DROP tables in the recycle bin, what should I do if I want to flash back to 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 easy to handle. You can directly specify the recyclebin name to flash back.

SQL> flashback table "BIN$BLmi9vlwN3TgUKjAgYxoiA==$0" to before drop;

Clear Recycle Bin

After the database object is deleted, the database will rename it to an object starting with BIN $. You can view its original object name through ORIGINAL_NAME. Remember, placing a table in the recycle bin does not release space in the original tablespace. If you want to delete the table completely without putting it into the recycle bin, you can use the following command to permanently delete the table. After this operation, you cannot use the flashback feature to flash back the table.

Drop table TABLE_NAME PURGE;

If the table is deleted in the database and the occupied space is not released, will the deleted table occupy the storage space when the free space is insufficient?

The answer is simple: when the tablespace is fully occupied by the recycle bin, so that the data file must be extended to accommodate more data, it can be said that the tablespace is under "Space pressure. In this case, the object is automatically cleared from the recycle bin in the first-in-first-out mode. Before deleting a table, related objects (such as indexes) are deleted.

Similarly, the space pressure may be caused by the user quota defined by a specific tablespace. The tablespace may have enough free space, but the user may have used up the part allocated in the tablespace. In this case, Oracle automatically clears the objects belonging to the user in the tablespace.

In addition, you can manually control the recycle bin in several ways. If you need to clear a table named TEST from the recycle bin after deleting it, you can run

Purge table TABLE_NAME;

Or use the name in its recycle bin:

Purge table "BIN $04 LhcpndanfgMAAAAAANPw ==0 0 ";

This command will delete the table TEST and all related objects, such as indexes and constraints, from the recycle bin, thus saving space. However, to permanently delete an index from the recycle bin, run the following command:

Purge index IN_TEST1_O1;

This command only deletes the index and keeps the copy of the table in the recycle bin. Sometimes clearing at a higher level may be useful. For example, you may want to clear all objects in the tablespace USERS recycle bin. Run the following command:

Purge tablespace users;

You may want to clear the recycle bin only for specific users in the tablespace. In a data warehouse environment, you can create and delete many temporary tables. This method may be useful. You can change the preceding command to clear only specific users:

Purge tablespace users user scott;

To release the space occupied by the entire recycle bin, run the following command to clear the recycle bin:

Purge recyclebin;

Remember that purge recyclebin only clears objects in the current user recycle bin. The objects in DBA_RECYCLEBIN are not deleted. To clear objects in the current database recycle bin, you must use the following command (DBA permission)

PURGE DBA_RECYCLEBIN

Notes for Flashback Drop

1: it can only be used for non-system tablespaces and locally managed tablespaces.

As shown below, in the system tablespace, the table objects are deleted from the system rather than 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 reference constraints of the object will not be restored. The foreign key constraints pointing to the object need to be rebuilt.

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

4: When you delete a table, the Materialized View dependent on the table will also be deleted at the same time, but the materialized view will not be placed in recycle binzhong. Therefore, when you execute flashback drop,

The Materialized View dependent on it cannot be restored. The DBA must be manually rebuilt.

5: only query is supported for objects in the Recycle Bin. Other DML and DDL operations are not supported.

References:

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

Automatically clear oracle Recycle Bin

With its own mechanism, it will be automatically cleared.
But the details cannot be remembered. I was a little impressed with this when I was learning ocp.

Oracle-understanding of the lock mechanism-

1 Introduction-basic concepts of database locks
To ensure the correctness of concurrent users when accessing the same database object (that is, no loss changes, repeatable reads, and no dirty data read), the database introduces a lock mechanism. There are two basic lock types: Exclusive locks (marked as X locks) and Share locks (marked as S locks ).
Exclusive lock: If transaction T applies the X lock to data D, no other transaction can apply any type lock to D Until T releases the X lock on D; generally, an exclusive lock must be applied to the data before the data is modified. Therefore, an exclusive lock is also called a write lock.
Share lock: If transaction T adds S lock to data D, other transactions can only apply S lock to D, but cannot apply X lock until T releases S lock on D; generally, you need to apply a shared lock to the data before reading the data. Therefore, a shared lock is also called a read lock.
2 Introduction to Oracle multi-granularity blocking Mechanism
Oracle Database locks can be divided into the following categories based on different protected objects:
(1) DML lock (data locks): used to protect data integrity;
(2) DDL lock (dictionary locks): used to protect the structure of database objects (such as the schema definitions of tables, views, and indexes );
(3) internal locks and l a t c h es (internal locks and latches): protects the internal database structure;
(4) distributed locks: Used in OPS (Parallel Server;
(5) PCM locks: Used in OPS (Parallel Server.
This article mainly discusses DML (also known as data locks) locks. From the perspective of the lock granularity (the size of the Lock Object), Oracle DML locks have two levels: Row-level locks and table-level locks.
2.1 Oracle TX locks (Row-level locks and transaction locks)
Many technical staff who are not familiar with Oracle may think that each TX lock represents a blocked data row. The original meaning of TX is Transaction. When a Transaction executes data changes (Insert, Update, Delete) for the first time or uses SELECT... When a for update statement is queried, it acquires a TX (transaction) lock until the transaction ends (executing the COMMIT or ROLLBACK operation. Therefore, a TX lock can correspond to multiple data rows locked by the transaction.
Each row of Oracle data has a flag to indicate whether the row data is locked. Unlike other DBMS (database management systems), Oracle creates a chain table to maintain the locked data of each row, which greatly reduces the row-Level Lock maintenance overhead, to a large extent, it avoids the insufficient number of locks that often occur when other database systems use row-level blocking. Once the lock mark on the Data row is set to a bit, it indicates that the row data is locked by X, and Oracle does not have the S lock on the Data row.
2.2 tmlock (Table-Level Lock)
2.2.1 introduction of intention lock
A table is composed of rows. When locking a table, we need to check whether the lock application is compatible with the original table-Level Lock, check whether the lock is compatible with the lock on each row in the table. For example, a transaction needs to apply the S lock to a table. If an row in the table has been locked by another transaction, the application for the lock should also be blocked. If there is a lot of data in the table, the overhead of checking the lock mark row by row will be very large, and the system performance will be affected. To solve this problem, a new lock type can be introduced at the table level to indicate the locking of the row to which the lock belongs. This introduces the concept of "intention lock.
The intention lock means that if an intention lock is applied to a node, the lower node of the node is being locked, the intention lock must be applied to its upper-Layer Nodes first. For example, when locking any row in the table, you must first apply an intention lock to the table where the row is located, and then apply a lock to the row. In this way, when a transaction locks a table, the lock flag of each record in the table is no longer required, and the system efficiency is greatly improved.
2.2.2 type of intention lock
Two basic lock types (S lock and X lock) can naturally generate two intention locks:
Intention sharing lock (Intent Shar ...... remaining full text>