ORACLE回收站的基本操作

本篇内容介绍了“ORACLE回收站的基本操作”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！回收站开启和关闭分为会话级别和系统级别。会话级别的命令如下：ALTER SESSION SET recyclebin = ON;ALTER SESSION SET recyclebin = OFF;系统级别的命令如下：ALTER SYSTEM SET recyclebin = ON SCOPE =
SPFILE;ALTER SYSTEM SET recyclebin = OFF SCOPE =
SPFILE;在系统级别开启和关闭回收站需要重启数据库使其生效。可以通过如下命令查看回收站是否开启：SQL> SHOW PARAMETER recyclebin;可以通过上面三个同义词查看回收站内容，其中RECYCLEBIN和USER_RECYCLEBIN相同，都是查看回收站中当前用户的对象，而DBA_RECYCLEBIN是查看所有回收站的对象，需要DBA角色或相应权限才能访问该视图。查看三个同义词的元数据：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
“SYS”.”USER_RECYCLEBINSQL> 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
“SYS”.”DBA_RECYCLEBIN”RECYCLEBIN和USER_RECYCLEBIN是同一个视图SYS.USER_RECYCLEBIN的同义词，DBA_RECYCLEBIN是SYS.DBA_RECYCLEBIN的同义词。查看视图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.type#, 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.spacefrom sys.”_CURRENT_EDITION_OBJ”
o, sys.recyclebin$ r, sys.ts$ twhere r.owner# = userenv(‘SCHEMAID’) and o.obj# = r.obj# and r.ts# = t.ts#(+)查看视图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.type#, 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.spacefrom sys.”_CURRENT_EDITION_OBJ”
o, sys.recyclebin$ r, sys.user$ u, sys.ts$ twhere o.obj# = r.obj# and r.owner# = u.user# and r.ts# = t.ts#(+)比较上面两个视图的定义，可以看到USER_RECYCLEBIN的where条件中有谓词条件“r.owner#
= userenv(‘SCHEMAID’)”，这就解释了USER_RECYCLEBIN只能查看回收站中关于当前用户的对象。根据需要，可以使用USER_RECYCLEBIN（RECYCLEBIN）或DBA_RECYCLEBIN查看回收站中的对象。他们包含的字段除了DBA_RECYCLEBIN多了OWNER字段外其他的都是相同的，常查询的几个字段如下：SELECT owner,object_name,original_name,type,ts_name,droptime FROM
dba_recyclebin;或SELECT object_name,original_name,type,ts_name,droptime FROM recyclebin;除了上述方法外，也可以用“SHOW
recyclebin”命令查看当前用户在回收站中的表：SQL> show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT
TYPE DROP TIME—————-
—————————— ———— ——————-TESTBIN BIN$WEHQbKB6WXbgU990QAqDfQ==$0 TABLE 2017-09-03:11:44:41这里值得说明的是，只有“DROP
TABLE”语句才能将对象放到回收站，这里的对象包含了要删除的表和表相关的其他对象，如索引、触发器等。用视图或同义词查询回收站的对象，是可以看到表类型以外的对象的，而“SHOW recyclebin”方法只能看到表对象。首先我们来做一段测试。测试前，我们先看看回收站中有什么：SQL> show userUSER 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:11:44:41 回收站中有一个原表名为TESTBIN的对象。创建一个表名为TESTBIN的表，并为其创建一个主键索引和一个普通索引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);接下来，我们删除索引ind_testbin_name，看看回收站中是否会增加这个索引的对象：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:11:44:41 回收站中并没有增加删除的索引信息。重新创建索引已进行后面的测试SQL> create index ind_testbin_name on
testbin(name);接下来删除表TESTBIN，看看与之关联的对象是否会被放入回收站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:11:44:41BIN$WEHQbKB/WXbgU990QAqDfQ==$0
IND_TESTBIN_NAME INDEX 2017-09-03:12:20:34BIN$WEHQbKCAWXbgU990QAqDfQ==$1
BIN$WEHQbKB8WXbgU990QAqDfQ==$0 INDEX 2017-09-03:12:20:34BIN$WEHQbKCBWXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03:12:20:34可以看到，回收站中对了三条记录，分别是删除的表和表的索引。也同时看到，有两条记录对应的original_name字段值为TESTBIN。清理回收站分为四个级别：表级别、用户级别、表空间级别、清空级别。可以单独清理回收站中某个表的信息，同时该表相关的对象也会被清理掉。命令如下：SQL> PURGE TABLE testbin;或SQL> PURGE TABLE “BIN$WEHQbKCBWXbgU990QAqDfQ==$0”;非表的对象是不能单独清理的：SQL> PURGE TABLE IND_TESTBIN_NAME;PURGE TABLE IND_TESTBIN_NAME*ERROR at line 1:ORA-38307: object not in RECYCLE BIN下面是接着上面的测试一个测试：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:12:20:34BIN$WEHQbKCAWXbgU990QAqDfQ==$1
BIN$WEHQbKB8WXbgU990QAqDfQ==$0 INDEX 2017-09-03:12:20:34BIN$WEHQbKCBWXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03:12:20:34测试发现，清理回收站中的信息时，如果对应了多条记录，会删除最早的一条记录。再删一次：SQL&gt免费云主机域名; PURGE TABLE testbin;Table purged.SQL> SELECT
object_name,original_name,type,droptime FROM recyclebin;no rows selected表和表相关的索引都被清理了。用户级别清理回收站是指只清理回收站中当前用户的对象。命令如下：PURGE recyclebin;或PURGE user_recyclebin;测试如下：向回收站中注入TEST用户的记录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:46:32能否向回收站中注入SYS用户的记录？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测试表名，删除SYS用户的表，并不会将表放到回收站。经过测试，还发现SYSTEM用户的表被删除时也不会放到回收站。创建新的用户TEST2，并向回收站中注入TEST2的记录SQL> show userUSER 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:12:59:27查看DBA_RECYCLEBIN中的记录SQL> show userUSER is “SYS”SQL> SELECT
owner,object_name,original_name,type,droptime FROM dba_recyclebin;OWNER OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME——————————
—————————— ——————————–
————————- ——————-TEST BIN$WEHQbKCCWXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03:12:46:32TEST2 BIN$WELbyCwFaGTgU990QAoh8w==$0 TEST2BIN TABLE 2017-09-03:12:59:27能查看到TEST和TEST2的记录。在TEST2用户下清理回收站SQL> show userUSER is “TEST2”SQL> PURGE recyclebin;Recyclebin purged.SQL> SELECT
object_name,original_name,type,droptime FROM recyclebin;no rows selected清理完成后，TEST2下查看不到自己的记录了。用DBA_RECYCLEBIN可以查看到除TEST2以外的其他用户的记录：SQL> show userUSER is “SYS”SQL> SELECT
owner,object_name,original_name,type,droptime FROM dba_recyclebin;OWNER OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME——————————
—————————— ——————————–
————————- ——————-TEST BIN$WEHQbKCCWXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03:12:46:32表空间级别删除回收站中记录，需要知道表空间，命令如下：PURGE TABLESPACE test;也可以删除指定表空间下指定用户的记录，命令如下：PURGE TABLESPACE test USER test;测试如下：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:46:32SQL> PURGE TABLESPACE test;Tablespace purged.SQL> SELECT
object_name,original_name,type,ts_name,droptime FROM dba_recyclebin;no rows selected清空回收站命令如下：PURGE dba_recyclebin;该命令需要在SYSDBA用户下执行。从回收站中恢复表用到的命令为flashback
table，如下：flashback
table testbin to
before drop;测试如下：创建对象并dropSQL> show recyclebin;SQL> show userUSER 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,’test recyclebin’);1 row created.SQL> commit;Commit complete.SQL> drop table testbin;Table dropped.上面创建了两个相同表名的表，并将它们都放到了回收站中，其中后放入回收站的表上有一个主键索引和普通索引。查看用户的回收站SQL> show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT
TYPE DROP TIME—————-
—————————— ———— ——————-TESTBIN BIN$WEO4ydVndITgU990QApxdg==$0 TABLE 2017-09-03:14:03:50TESTBIN BIN$WEO4ydVjdITgU990QApxdg==$0 TABLE 2017-09-03:14:01:14SQL> 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:14:03:50BIN$WEO4ydVmdITgU990QApxdg==$0
IND_PK_TESTBIN INDEX 2017-09-03:14:03:50BIN$WEO4ydVjdITgU990QApxdg==$0 TESTBIN TABLE 2017-09-03:14:01:14BIN$WEO4ydVndITgU990QApxdg==$0 TESTBIN TABLE 2017-09-03:14:03:50确认了创建的对象都已进入了回收站。接下来就要测试flashback table了。第一次flashback table从回收站中恢复表：SQL> flashback table testbin to before
drop;Flashback complete.恢复之后，查看回收站：SQL> SELECT
object_name,original_name,type,droptime FROM recyclebin;OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME——————————
——————————– ————————-
——————-BIN$WEO4ydVjdITgU990QApxdg==$0 TESTBIN TABLE 2017-09-03:14:01:14只剩最早删除的记录了，最新的表和表的索引都已从回收站恢复，查看表记录：SQL> select * from testbin; ID
NAME———- ——————– 123 test recyclebin第二次flashback table如果想要恢复回收站中剩下的表TESTBIN，显然会出现错误，因为相同的表已经存在了。测试如下：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此时，如果想恢复这个表，需要将它rename。命令如下：flashback
table testbin to
before drop rename to testbin_old;测试如下：SQL> flashback table testbin to before
drop rename to testbin_old;Flashback complete.SQL> select * from testbin_old; ID———- 1值得说明的是，能否flashback table取决于回收站中是否存在该记录，与是否开启了回收站无关。“ORACLE回收站的基本操作”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注百云网站，小编将为大家输出更多高质量的实用文章！

相关推荐: Oracle12c数据库怎么查看表空间使用情况

本篇内容主要讲解“Oracle12c数据库怎么查看表空间使用情况”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Oracle12c数据库怎么查看表空间使用情况”吧! 通过以下sql语句可以查看Oracle12c数据库…