Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the basic operation of the ORACLE Recycle Bin". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1 Recycle Bin is open and closed

The recycle bin is opened and closed at the session level and the system level. The session-level commands are as follows:

ALTER SESSION SET recyclebin = ON

ALTER SESSION SET recyclebin = OFF

The system-level commands are as follows:

ALTER SYSTEM SET recyclebin = ON SCOPE = SPFILE

ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE

Opening and closing the Recycle Bin at the system level requires rebooting the database for it to take effect.

You can check whether the Recycle Bin is open by using the following command:

SQL > SHOW PARAMETER recyclebin

2 View and empty the Recycle Bin 2.1 RECYCLEBIN, USER_RECYCLEBIN, DBA_RECYCLEBIN

You can view the contents of the Recycle Bin through the above three synonyms, where RECYCLEBIN and USER_RECYCLEBIN are the same objects for viewing the current user in the Recycle Bin, while DBA_RECYCLEBIN is the object for viewing all Recycle Bins, which requires the DBA role or corresponding permissions to access the view. View the metadata for three synonyms:

SQL > select dbms_metadata.get_ddl ('SYNONYM','RECYCLEBIN','PUBLIC') from dual

DBMS_METADATA.GET_DDL ('SYNONYM','RECYCLEBIN','PUBLIC')

CREATE OR REPLACE PUBLIC SYNONYM "RECYCLEBIN" FOR "SYS".

USER_RECYCLEBIN "

SQL > select dbms_metadata.get_ddl ('SYNONYM','USER_RECYCLEBIN','PUBLIC') from dual

DBMS_METADATA.GET_DDL ('SYNONYM','USER_RECYCLEBIN','PUBLIC')

CREATE OR REPLACE PUBLIC SYNONYM "USER_RECYCLEBIN" FOR "S

YS "." USER_RECYCLEBIN

SQL > select dbms_metadata.get_ddl ('SYNONYM','DBA_RECYCLEBIN','PUBLIC') from dual

DBMS_METADATA.GET_DDL ('SYNONYM','DBA_RECYCLEBIN','PUBLIC')

CREATE OR REPLACE PUBLIC SYNONYM "DBA_RECYCLEBIN" FOR "SY

S "." DBA_RECYCLEBIN "

RECYCLEBIN and USER_RECYCLEBIN are synonyms for the same view SYS.USER_RECYCLEBIN, and DBA_RECYCLEBIN is synonymous with SYS.DBA_RECYCLEBIN.

View the definition of the view SYS.USER_RECYCLEBIN:

CREATE OR REPLACE FORCE VIEW "SYS". "USER_RECYCLEBIN" ("OBJECT_NAME", "ORIGINAL_NAME", "OPERATION", "TYPE", "TS_NAME", "CREATETIME", "DROPTIME", "DROPSCN", "PARTITION_NAME", "CAN_UNDROP", "CAN_PURGE", "RELATED", "BASE_OBJECT", "PURGE_OBJECT", "SPACE") AS

Select o.name, r.original_name

Decode (r.operation, 0, 'DROP', 1,' TRUNCATE', 'UNDEFINED')

Decode (r. Typewriter, 1, 'TABLE', 2,' INDEX', 3, 'INDEX'

4, 'NESTED TABLE', 5,' LOB', 6, 'LOB INDEX'

7, 'DOMAIN INDEX', 8,' IOT TOP INDEX'

9, 'IOT OVERFLOW SEGMENT', 10,' IOT MAPPING TABLE'

11, 'TRIGGER', 12,' CONSTRAINT', 13, 'Table Partition'

14, 'Table Composite Partition', 15,' Index Partition'

16, 'Index Composite Partition', 17,' LOB Partition'

18, 'LOB Composite Partition'

'UNDEFINED')

T.name

To_char (o.ctime, 'YYYY-MM-DD:HH24:MI:SS')

To_char (r.droptime, 'YYYY-MM-DD:HH24:MI:SS')

R.dropscn, r.partition_name

Decode (bitand (r.flags, 4), 0, 'NO', 4,' YES', 'NO')

Decode (bitand (r.flags, 2), 0, 'NO', 2,' YES', 'NO')

R.related, r.bo, r.purgeobj, r.space

From sys. "_ CURRENT_EDITION_OBJ" o, sys.recyclebin$ r, sys.ts$ t

Where r.owner# = userenv ('SCHEMAID')

And o.obj# = r.obj#

And r.ts# = t.ts# (+)

View the definition of the view SYS.DBA_RECYCLEBIN:

CREATE OR REPLACE FORCE VIEW "SYS". "DBA_RECYCLEBIN" ("OWNER", "OBJECT_NAME", "ORIGINAL_NAME", "OPERATION", "TYPE", "TS_NAME", "CREATETIME", "DROPTIME", "DROPSCN", "PARTITION_NAME", "CAN_UNDROP", "CAN_PURGE", "RELATED", "BASE_OBJECT", "PURGE_OBJECT", "SPACE") AS

Select u.name, o.name, r.original_name

Decode (r.operation, 0, 'DROP', 1,' TRUNCATE', 'UNDEFINED')

Decode (r. Typewriter, 1, 'TABLE', 2,' INDEX', 3, 'INDEX'

4, 'NESTED TABLE', 5,' LOB', 6, 'LOB INDEX'

7, 'DOMAIN INDEX', 8,' IOT TOP INDEX'

9, 'IOT OVERFLOW SEGMENT', 10,' IOT MAPPING TABLE'

11, 'TRIGGER', 12,' CONSTRAINT', 13, 'Table Partition'

14, 'Table Composite Partition', 15,' Index Partition'

16, 'Index Composite Partition', 17,' LOB Partition'

18, 'LOB Composite Partition'

'UNDEFINED')

T.name

To_char (o.ctime, 'YYYY-MM-DD:HH24:MI:SS')

To_char (r.droptime, 'YYYY-MM-DD:HH24:MI:SS')

R.dropscn, r.partition_name

Decode (bitand (r.flags, 4), 0, 'NO', 4,' YES', 'NO')

Decode (bitand (r.flags, 2), 0, 'NO', 2,' YES', 'NO')

R.related, r.bo, r.purgeobj, r.space

From sys. "_ CURRENT_EDITION_OBJ" o, sys.recyclebin$ r, sys.user$ u, sys.ts$ t

Where o.obj# = r.obj#

And r.owner# = u.user#

And r.ts# = t.ts# (+)

Comparing the definitions of the above two views, you can see that there is a predicate condition "r.owner# = userenv ('SCHEMAID')" in USER_RECYCLEBIN 's where condition, which explains why USER_RECYCLEBIN can only view objects about the current user in the recycle bin.

2.2 View objects in the Recycle Bin

As needed, you can use USER_RECYCLEBIN (RECYCLEBIN) or DBA_RECYCLEBIN to view objects in the Recycle Bin. The fields they contain are all the same except for DBA_RECYCLEBIN with more OWNER fields. Several fields that are often queried are as follows:

SELECT owner,object_name,original_name,type,ts_name,droptime FROM dba_recyclebin

Or

SELECT object_name,original_name,type,ts_name,droptime FROM recyclebin

In addition to the above methods, you can also use the "SHOW recyclebin" command to view the current user's table in the Recycle Bin:

SQL > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

TESTBIN BIN$WEHQbKB6WXbgU990QAqDfQ==$0 TABLE 2017-09-03 Fran 11UR 44MU 41

It is worth noting that only the "DROP TABLE" statement can place objects in the Recycle Bin, where the objects contain the table to be deleted and other table-related objects, such as indexes, triggers, and so on. When you query objects in the Recycle Bin with views or synonyms, you can see objects other than the table type, while the "SHOW recyclebin" method can only see table objects.

2.3 Storage mechanism of the Recycle Bin

First of all, let's do a test.

Before testing, let's take a look at what's in the Recycle Bin:

SQL > show user

USER is "TEST"

SQL > SELECT object_name,original_name,type,droptime FROM recyclebin

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME

BIN$WEHQbKB6WXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03 Fran 11UR 44MU 41

There is an object in the Recycle Bin with the original table name TESTBIN.

Create a table named TESTBIN, and create a primary key index and a normal index for it

Create table testbin (

Id number (3)

Name varchar2 (20)

Constraint pk_testbin primary key (id)

Using index (create unique index ind_pk_testbin on testbin (id)

Create index ind_testbin_name on testbin (name)

Next, let's delete the index ind_testbin_name and see if objects with this index will be added to the Recycle Bin:

SQL > drop index ind_testbin_name

Index dropped.

SQL > SELECT object_name,original_name,type,droptime FROM recyclebin

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME

BIN$WEHQbKB6WXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03 Fran 11UR 44MU 41

Deleted index information is not added to the Recycle Bin. Recreating the index has been tested later

SQL > create index ind_testbin_name on testbin (name)

Next, delete the table TESTBIN to see if the objects associated with it will be placed in the Recycle Bin.

SQL > drop table testbin

Table dropped.

SQL > SELECT object_name,original_name,type,droptime FROM recyclebin

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME

-

BIN$WEHQbKB6WXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03 Fran 11UR 44MU 41

BIN$WEHQbKB/WXbgU990QAqDfQ==$0 IND_TESTBIN_NAME INDEX 2017-09-03 purl 12 Vera 20 purl 34

BIN$WEHQbKCAWXbgU990QAqDfQ==$1 BIN$WEHQbKB8WXbgU990QAqDfQ==$0 INDEX 2017-09-03 purl 12 Vera 20 purl 34

BIN$WEHQbKCBWXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03 purl 12 Vera 20 purl 34

As you can see, there are three records in the Recycle Bin, namely the deleted table and the index of the table. At the same time, you can see that there are two records with corresponding original_name field values of TESTBIN.

2.4 empty the Recycle Bin

Cleaning the Recycle Bin is divided into four levels: table level, user level, tablespace level, and emptying level.

2.4.1 Table level:

The information of a table in the Recycle Bin can be cleaned separately, and the objects related to that table will also be cleaned up. The command is as follows:

SQL > PURGE TABLE testbin

Or

SQL > PURGE TABLE "BIN$WEHQbKCBWXbgU990QAqDfQ==$0"

Non-table objects cannot be cleaned separately:

SQL > PURGE TABLE IND_TESTBIN_NAME

PURGE TABLE IND_TESTBIN_NAME

*

ERROR at line 1:

ORA-38307: object not in RECYCLE BIN

Here is a test that follows the above test:

SQL > PURGE TABLE testbin

Table purged.

SQL > SELECT object_name,original_name,type,droptime FROM recyclebin

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME

-

BIN$WEHQbKB/WXbgU990QAqDfQ==$0 IND_TESTBIN_NAME INDEX 2017-09-03 purl 12 Vera 20 purl 34

BIN$WEHQbKCAWXbgU990QAqDfQ==$1 BIN$WEHQbKB8WXbgU990QAqDfQ==$0 INDEX 2017-09-03 purl 12 Vera 20 purl 34

BIN$WEHQbKCBWXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03 purl 12 Vera 20 purl 34

The test found that when cleaning up the information in the Recycle Bin, if there are multiple records, the earliest record will be deleted. Delete it again:

SQL > PURGE TABLE testbin

Table purged.

SQL > SELECT object_name,original_name,type,droptime FROM recyclebin

No rows selected

Tables and table-related indexes are all cleaned up.

2.4.2 user level

User-level cleanup of the Recycle Bin means that only the objects of the current user in the Recycle Bin are cleaned. The command is as follows:

PURGE recyclebin

Or

PURGE user_recyclebin

The tests are as follows:

Inject TEST user records into the Recycle Bin

SQL > show user

USER is "TEST"

SQL > create table testbin (col1 number)

Table created.

SQL > drop table testbin

Table dropped.

SQL > SELECT object_name,original_name,type,droptime FROM recyclebin

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME

-

BIN$WEHQbKCCWXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03 12 14 14 40 32

Can I inject SYS user records into the Recycle Bin?

SQL > show user

USER is "SYS"

SQL > create table sysbin (id number)

Table created.

SQL > drop table sysbin

Table dropped.

SQL > SELECT object_name,original_name,type,droptime FROM recyclebin

No rows selected

Test the table name, delete the SYS user's table, and do not put the table in the Recycle Bin. After testing, it is also found that SYSTEM users' tables will not be placed in the Recycle Bin when they are deleted.

Create a new user TEST2 and inject TEST2 records into the Recycle Bin

SQL > show user

USER is "TEST2"

SQL > create table test2bin (id number)

Table created.

SQL > drop table test2bin

Table dropped.

SQL > SELECT object_name,original_name,type,droptime FROM recyclebin

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME

-

BIN$WELbyCwFaGTgU990QAoh8w==$0 TEST2BIN TABLE 2017-09-03 Fraser 12 Fringe 59 purl 27

View records in DBA_RECYCLEBIN

SQL > show user

USER is "SYS"

SQL > SELECT owner,object_name,original_name,type,droptime FROM dba_recyclebin

OWNER OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME

-

TESTBIN $WEHQbKCCWXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03

TEST2BIN $WELbyCwFaGTgU990QAoh8w==$0 TEST2BIN TABLE 2017-09-03

We can see the records of TEST and TEST2.

Clean the Recycle Bin under TEST2 users

SQL > show user

USER is "TEST2"

SQL > PURGE recyclebin

Recyclebin purged.

SQL > SELECT object_name,original_name,type,droptime FROM recyclebin

No rows selected

After the cleanup is complete, you can't view your own records under TEST2. With DBA_RECYCLEBIN, you can view the records of users other than TEST2:

SQL > show user

USER is "SYS"

SQL > SELECT owner,object_name,original_name,type,droptime FROM dba_recyclebin

OWNER OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME

-

TESTBIN $WEHQbKCCWXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03

2.4.3 Tablespace level

To delete records in the Recycle Bin at the tablespace level, you need to know the tablespace. The command is as follows:

PURGE TABLESPACE test

You can also delete the records of the specified user under the specified tablespace, as follows:

PURGE TABLESPACE test USER test

The tests are as follows:

SQL > SELECT object_name,original_name,type,ts_name,droptime FROM dba_recyclebin

OBJECT_NAME ORIGINAL_NAME TYPE TS_NAME DROPTIME

-

BIN$WEHQbKCCWXbgU990QAqDfQ==$0 TESTBIN TABLE TEST 2017-09-03 12 14 14 40 32

SQL > PURGE TABLESPACE test

Tablespace purged.

SQL > SELECT object_name,original_name,type,ts_name,droptime FROM dba_recyclebin

No rows selected

Empty the Recycle Bin

The command is as follows:

PURGE dba_recyclebin

This command needs to be executed under the SYSDBA user.

3 restore tables from the Recycle Bin

The command used to recover the table from the Recycle Bin is flashback table, as follows:

Flashback table testbin to before drop

The tests are as follows:

Create an object and drop

SQL > show recyclebin

SQL > show user

USER is "TEST"

SQL > create table testbin (id number)

Table created.

SQL > insert into testbin values (1)

1 row created.

SQL > commit

Commit complete.

SQL > drop table testbin

Table dropped.

SQL > create table testbin (

Id number (3)

Name varchar2 (20)

Constraint pk_testbin primary key (id)

Using index (create unique index ind_pk_testbin on testbin (id)); 2 3 4 5

Table created.

SQL > create index ind_testbin_name on testbin (name)

Index created.

SQL > insert into testbin values (123 perfect test recyclebin')

1 row created.

SQL > commit

Commit complete.

SQL > drop table testbin

Table dropped.

Above, two tables with the same table name are created and placed in the Recycle Bin, where the table placed in the Recycle Bin has a primary key index and a normal index.

View the user's Recycle Bin

SQL > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

-

TESTBIN BIN$WEO4ydVndITgU990QApxdg==$0 TABLE 2017-09-03 purl 14 purl 03purl 50

TESTBIN BIN$WEO4ydVjdITgU990QApxdg==$0 TABLE 2017-09-03 purl 14 purl 01ordin14

SQL > SELECT object_name,original_name,type,droptime FROM recyclebin

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME

-

BIN$WEO4ydVldITgU990QApxdg==$0 IND_TESTBIN_NAME INDEX 2017-09-03 purl 14 purl 03purl 50

BIN$WEO4ydVmdITgU990QApxdg==$0 IND_PK_TESTBIN INDEX 2017-09-03 purl 14 purl 03purl 50

BIN$WEO4ydVjdITgU990QApxdg==$0 TESTBIN TABLE 2017-09-03 purl 14 purl 01ordin14

BIN$WEO4ydVndITgU990QApxdg==$0 TESTBIN TABLE 2017-09-03 purl 14 purl 03purl 50

It is confirmed that the created objects have entered the Recycle Bin. The next step is to test flashback table.

The first flashback table

Restore tables from the Recycle Bin:

SQL > flashback table testbin to before drop

Flashback complete.

After recovery, check the Recycle Bin:

SQL > SELECT object_name,original_name,type,droptime FROM recyclebin

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME

-

BIN$WEO4ydVjdITgU990QApxdg==$0 TESTBIN TABLE 2017-09-03 purl 14 purl 01ordin14

Only the earliest deleted records are left, and the latest tables and table indexes have been restored from the Recycle Bin. View the table records:

SQL > select * from testbin

ID NAME

--

123 test recyclebin

The second flashback table

If you want to recover the remaining table TESTBIN in the Recycle Bin, there will obviously be an error because the same table already exists. The tests are as follows:

SQL > flashback table testbin to before drop

Flashback table testbin to before drop

*

ERROR at line 1:

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

At this point, if you want to restore the table, you need to rename it. The command is as follows:

Flashback table testbin to before drop rename to testbin_old

The tests are as follows:

SQL > flashback table testbin to before drop rename to testbin_old

Flashback complete.

SQL > select * from testbin_old

ID

-

one

It is worth noting that flashback table depends on whether the record exists in the Recycle Bin and has nothing to do with whether the Recycle Bin is open.

This is the end of the introduction to the basic Operation of the ORACLE Recycle Bin. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.