Shulou(Shulou.com)06/01 Report--

This function began with 10g.

(1) what is Recycle Bin

In fact, Recycle Bin is just a data dictionary table that holds objects that are drop. Therefore, you can query the information in the recycling bin with the following statement: select * from recyclebin

Unless you have sysdba permission, each user can only see his or her own object. So, for users, it seems that everyone has their own recycle bin. Even if users have permission to delete other schema objects, they can only see their own objects in recyclebin. [@ more@]

Do a little experiment:

SQL > conn ning/ning

Connected.

SQL > drop table test

Table has been deleted.

SQL > drop table test.test

Table has been deleted.

SQL > select object_name,original_name from recyclebin

OBJECT_NAME ORIGINAL_NAME

BIN$xJlIkIFVR/yau1Qb8ocxxQ==$0 TEST

SQL > con test/test

Connected.

SQL > select object_name,original_name from recyclebin

OBJECT_NAME ORIGINAL_NAME

BIN$1PKEiRExRN2g3tqEEWLfyw==$0 TEST

The following drop do not put related objects into the RecycleBin:

The following drop do not put related objects into the RecycleBin:

Drop tablespace: clears all objects in the RecycleBin that belong to the tablespace

Drop user: clears all objects in the RecycleBin that belong to this user

Drop cluster: clears all member objects in the RecycleBin that belong to the cluster

Drop type: clears all objects in the RecycleBin that depend on this type

Objects in RecycleBin are automatically renamed by the system according to the rules to prevent naming conflicts. The naming format is: BIN$unique_id$version

Where unique_id is the unique identity of the 26-character object, and version is the version number of the object in the database.

SQL > create table t (id int)

The table has been created.

SQL > drop table t

Table has been deleted.

SQL > select object_name,original_name from recyclebin

OBJECT_NAME ORIGINAL_NAME

BIN$+7J5MgT2RbCX+tka+zjpgA==$0 T

SQL > create table t (id int)

The table has been created.

SQL > drop table t

Table has been deleted.

SQL > select object_name,original_name from recyclebin

OBJECT_NAME ORIGINAL_NAME

BIN$+7J5MgT2RbCX+tka+zjpgA==$0 T

BIN$pc+kkUM7QjuQeCDGtwlzgQ==$0 T

As you can see, after deleting table t, rebuild a table named t and delete it again, its unique_id is different.

At this point, make a restore and take a look:

SQL > select object_name,original_name from recyclebin

OBJECT_NAME ORIGINAL_NAME

BIN$+7J5MgT2RbCX+tka+zjpgA==$0 T

Here, Oracle selects the last deleted T-table restore.

Delete again:

SQL > drop table t

Table has been deleted.

SQL > select object_name,original_name from recyclebin

OBJECT_NAME ORIGINAL_NAME

BIN$4bNgZiMGTA63iwA5xflh6A==$0 T

BIN$+7J5MgT2RbCX+tka+zjpgA==$0 T

You can see that unique_id has changed again. Therefore, this naming method avoids the naming conflicts that may occur if the table is deleted and then the table of the same name is rebuilt.

two。 How to enable / disable RecycleBin

You can control whether the Recycle Bin feature is enabled by setting the initialization parameter recyclebin, which is enabled by default.

SQL > alter system set recyclebin=off

The system has changed.

SQL > alter system set recyclebin=on

The system has changed.

SQL > alter session set recyclebin=off

The session has changed.

SQL > alter session set recyclebin=on

The session has changed.

3. How to view information in RecycleBin

As mentioned earlier, users can view their own drop objects by querying recyclebin.

SQL > select object_name,object_type from dba_objects where object_name='RECYCLEB

IN'

OBJECT_NAME OBJECT_TYPE

RECYCLEBIN SYNONYM

SQL > select owner,synonym_name,table_owner,table_name from dba_synonyms where sy

Nonym_name='RECYCLEBIN'

OWNER SYNONYM_NAME TAB TABLE_NAME

-

PUBLIC RECYCLEBIN SYS USER_RECYCLEBIN

As you can see, recyclebin is actually a common synonym for user_recyclebin. You can also view all drop objects in the system through dba_recyclebin, but there is no all_recyclebin view here.

In addition, Sqlplus provides a special command:

SQL > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

TEST BIN$xJlIkIFVR/yau1Qb8ocxxQ==$0 TABLE 2006-12-13 purl 2200 purl 33

However, this command can only see the table of the drop, and the corresponding index of the table can only be obtained by query.

SQL > create table t (id int)

The table has been created.

SQL > create index ix_t on t (id)

The index has been created.

SQL > alter table t add constraint con_t_pk primary key (id) using index ix_t

The table has changed.

SQL > drop table t

Table has been deleted.

SQL > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

T BIN$w0Os4BFTSE+N81RO1pUHaw==$0 TABLE 2006-12-13 purl 22 purl 45 purl 58

SQL > select object_name,original_name,type from recyclebin

OBJECT_NAME ORIGINAL_NAME TYPE

-

BIN$dw0vOefrQPC5AAaFn7rkPg==$0 IX_T INDEX

BIN$w0Os4BFTSE+N81RO1pUHaw==$0 T TABLE

4. How to clear objects in RecycleBin

If there are too many things in the Recycle Bin, it is natural to clear them. Oracle10g adds a new keyword purge to perform the function of clearing recyclebin.

Purge table table_name can clear the specified table, where the table_name can be either the original name of the table or the name automatically named in the Recycle Bin according to the rules.

SQL > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

T BIN$+7J5MgT2RbCX+tka+zjpgA==$0 TABLE 2006-12-13 22 14 11 Switzerland 49

TEST BIN$RwhOW+KvTSmOznDipO+BOw==$0 TABLE 2006-12-13 purge 22 purl 30 purl 38

SQL > purge table test

The table has been cleared.

SQL > purge table BIN$+7J5MgT2RbCX+tka+zjpgA==$0

Purge table BIN$+7J5MgT2RbCX+tka+zjpgA==$0

*

An error occurred on line 1:

ORA-00933: the SQL command did not end correctly

SQL > purge table "BIN$+7J5MgT2RbCX+tka+zjpgA==$0"

The table has been cleared.

Purge tablespace tablespace_name clears all objects where RecycleBin belongs to the specified tablespace.

SQL > purge tablespace users

The tablespace has been cleared.

Purge tablespace tablespace_name user user_name clears all objects in the Recycle that belong to the specified tablespace and the specified user.

SQL > purge tablespace users user ning

The tablespace has been cleared.

Purge recyclebin clears all recyclebin objects that can be seen by the user executing the command. That is, ordinary users can clear objects that belong to them, while sysdba users can clear objects in all recyclebin.

SQL > purge recyclebin

The Recycle Bin is empty.

In addition, purge index clears the index object.

SQL > select object_name,original_name,type from recyclebin

OBJECT_NAME ORIGINAL_NAME TYPE

-

BIN$dw0vOefrQPC5AAaFn7rkPg==$0 IX_T INDEX

BIN$w0Os4BFTSE+N81RO1pUHaw==$0 T TABLE

SQL > purge index ix_t

Purge index ix_t

*

An error occurred on line 1:

ORA-00604: an error occurred in recursive SQL level 1

ORA-02429: cannot delete an index used to force a unique / primary key

Here, because ix_t is the index of the table primary key, it cannot be cleared separately.

SQL > purge recyclebin

The Recycle Bin is empty.

SQL > create table T1 (id int)

The table has been created.

SQL > create index ix_t1 on T1 (id)

The index has been created.

SQL > drop table T1

Table has been deleted.

SQL > select object_name,original_name,type from recyclebin

OBJECT_NAME ORIGINAL_NAME TYPE

-

BIN$s6zn2IT+Ree2+slP2dpa0A==$0 IX_T1 INDEX

BIN$j2O8O52CT3aoakF4LlAGfg==$0 T1 TABLE

SQL > purge index ix_t1

Index has been cleared.

5. How to restore objects in RecycleBin

Flashback table has been used in previous experiments to restore deleted table.

SQL > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

TEST BIN$xJlIkIFVR/yau1Qb8ocxxQ==$0 TABLE 2006-12-13 purl 2200 purl 33

SQL > flashback table test to before drop rename to test

Flashback complete.

SQL > desc test

Is the name empty? Types

-

ID NUMBER (38)

If you delete the table with the same name multiple times, the last deleted test table will be restored using the above statement. Here, you can also use the name given to table by RecycleBin to restore.

SQL > flashback table "BIN$GoZUqZ6GS222JZdcCtFlHQ==$0" to before drop rename to test

Flashback complete.

=

Flashback discard (Flashback Drop)

Flashback discarding is to save the replication of discarded database objects and their dependent objects in the Recycle Bin so that they can be recovered in time if necessary. Discarded objects are not deleted from the database until the Recycle Bin is emptied. This enables the database to recover tables that were deleted by accident or misoperation.

In order to better understand flashback discarding, you need to understand the details of the work of the Recycle Bin, as well as the storage, query and removal of discarded objects in the Recycle Bin, which will be described in the following sections.

17.5.1 Recycle Bin concept

The Recycle Bin (Recycle Bin) is the logical storage container for all discarded tables and their dependent objects. When a table is discarded (DROP), the Recycle Bin stores the table and its dependent objects in the Recycle Bin. Dependent objects for tables stored in the Recycle Bin include indexes, constraints, triggers, nested tables, large binary object (LOB) segments, and LOB index segments.

The Oracle Recycle Bin records the operation of the DROP statement made by the user in a system table, and the objects to be deleted are written into a data dictionary table. When it is determined that the deleted objects are no longer needed, you can use the PURGE command to clear the recycle bin space.

In order to avoid duplicate names of deleted tables and similar objects, after the deleted tables (and dependent objects) are placed in the Recycle Bin, the Oracle system converts the deleted object names. The name conversion format of the deleted object (such as a table) is as follows:

BIN$globalUID$version

L globalUID is a globally unique, 24-character-long identity object, which is used internally by Oracle and has no practical meaning to the user because it has nothing to do with the name of the object before it is deleted.

L $version is the version number assigned by the Oracle database.

17.5.2 use the Recycle Bin

If you want to restore an DROP-passed table, you can use the following statement:

SQL > FLASHBACK TABLE table_name TO BEFORE DROP

In order to help readers understand the operation process of the recycling bin in use, the detailed operation steps of the recycling bin are given below.

Example: this example gives the data preparation, delete table, query recycle bin information, recovery and query recovery.

(1) Connect to Oracle

[oracle@localhost ~] $sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0-Production on Sat Oct 20 16:32:00 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL > set line 120

SQL > set pagesize 50

SQL > show user

USER is "SCOTT"

SQL >

(2) prepare data

SQL > create table my_emp as select * from emp

Table created.

SQL > select count (*) from my_emp

COUNT (*)

-

fourteen

SQL >

(3) Delete the table structure

SQL > select * from tab

TNAME TABTYPE CLUSTERID

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP2 TABLE

MY_EMP TABLE

SALGRADE TABLE

SYS_TEMP_FBT TABLE

7 rows selected.

SQL > drop table my_emp

Table dropped.

SQL >

(4) data dictionary after deleting (DROP) table

SQL > select * from tab

TNAME TABTYPE CLUSTERID

BIN$POiMOEfPgU3gQAB/AQASlg==$0 TABLE

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP2 TABLE

SALGRADE TABLE

SYS_TEMP_FBT TABLE

7 rows selected.

It is important to note that when the MY_ EMP table is deleted, it becomes BIN$POiMOEfPgU3gQAB/ AQASlg==$0,version 0 in the database recycle bin.

(5) check the user_recyclebin Recycle Bin and you can see the corresponding record of the deleted table:

SQL > col object_name for A30

SQL > col original_name for A20

SQL > select object_name,original_name from user_recyclebin

OBJECT_NAME ORIGINAL_NAME

BIN$POiMOEfPgU3gQAB/AQASlg==$0 MY_EMP

SQL >

(6) using the records in user_recyclebin, use FLASHBACK to recover table MY_EMP from the Recycle Bin:

SQL > flashback table my_emp to before drop

Flashback complete.

SQL > select count (*) from my_emp

COUNT (*)

-

fourteen

SQL >

The above are the query results after the recovery is complete.

17.5.3 Recycle Bin and Space Utilization

The Recycle Bin is a logical storage container for discarding objects, based on the existing allocated space in the tablespace, which means that the system does not reserve space for the Recycle Bin. This makes the recycle bin space dependent on the available space in the existing tablespace (that is, the space occupied by the discarded table still needs to be included in the tablespace quota). Therefore, the minimum amount of time the discarded object will spend in the Recycle Bin is not always guaranteed.

If you do not clear the Recycle Bin, the discarded object will be saved in the Recycle Bin until the table space to which the discarded object belongs can no longer allocate a new storage area, which is called space pressure. Sometimes, the user's tablespace limit can also lead to the appearance of the space pressure state, even if there is still free space in the tablespace.

When space pressure occurs, Oracle overwrites some recycle bin objects to automatically reclaim tablespaces. Oracle selects discarded objects for deletion according to the first-in, first-out principle, so the objects that are first discarded are also the first to be cleared. The removal of objects is only to solve the problem of space pressure, so it will remove as few objects as possible to meet the requirements of space pressure. This processing not only maximizes the available time of the object in the Recycle Bin, but also reduces the performance impact of Oracle in transaction processing.

DBA needs to pay attention to the space utilization of the Recycle Bin and master the way to clear the Recycle Bin object so as to free up space, which can be done with the PURGE command. The PURGE command deletes the table or index from the Recycle Bin and frees up the space occupied by the table and index; you can also clear the entire recycle bin or clear all parts of the deleted table space with the PURGE command.

It is worth mentioning that when the deleted object is cleared with the PURGE command, the object is indeed completely erased and cannot be rebuilt.

There are several ways to clear objects and free up space in the Recycle Bin.

(1) use PURGE TABLE original_table_name.

The original_table_name here represents the name of the table before drop (source name), which allows you to permanently delete objects from the Recycle Bin and free up space.

(2) use PURGE TABLE recyclebin_object_name.

The recyclebin_object_name here represents the name of the object in the Recycle Bin, which allows you to permanently delete the object from the Recycle Bin and free up space.

(3) use PURGE TABLESPACE tablespace_name to clear all objects in a specific table space from the Recycle Bin.

This command clears all discarded and dependent objects from the specified tablespace. Because dependent objects (such as LOB, nested tables, indexes, partitions, and so on) are not necessarily stored in the same table space as the base table, this command removes dependent objects from their own table space.

(4) using PURGE TABLESPACE tablespace_name USER user_name, all discarded objects belonging to a particular user (including dependent objects of the base table, of course) are cleared from the Recycle Bin.

(5) use the command DROP USER user_name CASCADE to directly delete the specified user and all the objects to which he belongs.

In other words, the DROP USER command bypasses the Recycle Bin and deletes directly. At the same time, if there is also an object belonging to the user in the Recycle Bin, it will also be cleared from the Recycle Bin.

(6) use the PURGE RECYCLEBIN command to clear the user's own recycle bin.

This command clears all objects from the user Recycle Bin and frees up the space associated with them.

(7) PURGE DBA_RECYCLEBIN clears all objects from all users' recycle bin.

This command can efficiently completely empty the Recycle Bin, of course, you must have SYSDBA system administrative privileges to execute the command.

Example: query the contents of the current user's Recycle Bin, and then clear it with PURGE.

SQL > show user

USER is "SCOTT"

SQL > create table orcltest as select * from emp

Table created.

SQL > select * from tab

TNAME TABTYPE CLUSTERID

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP2 TABLE

MY_EMP TABLE

ORCLTEST TABLE

SALGRADE TABLE

SYS_TEMP_FBT TABLE

8 rows selected.

SQL > drop table orcltest

Table dropped.

SQL > select object_name,original_name from user_recyclebin

OBJECT_NAME ORIGINAL_NAME

BIN$POiMOEfQgU3gQAB/AQASlg==$0 ORCLTEST

SQL >

SQL > show user

USER is "SCOTT"

SQL > select object_name,original_name from user_recyclebin

OBJECT_NAME ORIGINAL_NAME

BIN$POiMOEfQgU3gQAB/AQASlg==$0 ORCLTEST

SQL > purge table orcltest

Table purged.

SQL > select object_name,original_name from user_recyclebin

No rows selected

FLASHBACK flashback table

Delete the data in the table by mistake today and flashback through FLASHBACK

Flashback table mmdb.column_class_def_t to timestamp to_timestamp ('2009-03-24 14-14-40-40-12-12-14-14-40-12-12-14-14-14-40-12-12-14-14-14-40-12-12-14-14-14-40-12-12-14-14-40-12-14-14-40-40-12-14-40-12-14-40-12-14-14-14-40-14-14-40-14-14-40-12-14-14-14-14-14-14-14-14-14-40-12-14-14-14-14-14-14-40-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-

Cannot flash the table because row movement is not enabled.

Run alter table mmdb.column_class_def_t enable row movement; and then run FLASHBACK,OK

Transferred from: http://hi.baidu.com/randie/blog/item/dc6c7118b999ab4943a9adc1.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.