ORACLE回收站的基本操作
本篇内容介绍了“ORACLE回收站的基本操作”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
创新互联专注于企业营销型网站建设、网站重做改版、贵池网站定制设计、自适应品牌网站建设、HTML5、商城网站定制开发、集团公司官网建设、外贸网站建设、高端网站制作、响应式网页设计等建站业务，价格优惠性价比高，为贵池等各大城市提供网站开发制作服务。
1 回收站开启和关闭
回收站开启和关闭分为会话级别和系统级别。会话级别的命令如下：
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; |
2 查看并清空回收站
2.1 RECYCLEBIN、USER_RECYCLEBIN、DBA_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 "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和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.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#(+) |
查看视图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.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#(+) |
比较上面两个视图的定义，可以看到USER_RECYCLEBIN的where条件中有谓词条件“r.owner# = userenv('SCHEMAID')”，这就解释了USER_RECYCLEBIN只能查看回收站中关于当前用户的对象。
2.2 查看回收站中的对象
根据需要，可以使用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”方法只能看到表对象。
2.3 回收站的存放机制
首先我们来做一段测试。
测试前，我们先看看回收站中有什么：
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: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:41 BIN$WEHQbKB/WXbgU990QAqDfQ==$0 IND_TESTBIN_NAME INDEX 2017-09-03:12:20:34 BIN$WEHQbKCAWXbgU990QAqDfQ==$1 BIN$WEHQbKB8WXbgU990QAqDfQ==$0 INDEX 2017-09-03:12:20:34 BIN$WEHQbKCBWXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03:12:20:34 |
可以看到，回收站中对了三条记录，分别是删除的表和表的索引。也同时看到，有两条记录对应的original_name字段值为TESTBIN。
2.4 清空回收站
清理回收站分为四个级别：表级别、用户级别、表空间级别、清空级别。
2.4.1 表级别：
可以单独清理回收站中某个表的信息，同时该表相关的对象也会被清理掉。命令如下：
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:34 BIN$WEHQbKCAWXbgU990QAqDfQ==$1 BIN$WEHQbKB8WXbgU990QAqDfQ==$0 INDEX 2017-09-03:12:20:34 BIN$WEHQbKCBWXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03:12:20:34 |
测试发现，清理回收站中的信息时，如果对应了多条记录，会删除最早的一条记录。再删一次：
SQL> PURGE TABLE testbin;
Table purged.
SQL> SELECT object_name,original_name,type,droptime FROM recyclebin;
no rows selected |
表和表相关的索引都被清理了。
2.4.2 用户级别
用户级别清理回收站是指只清理回收站中当前用户的对象。命令如下：
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 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:12:59:27 |
查看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 ------------------------------ ------------------------------ -------------------------------- ------------------------- ------------------- TEST BIN$WEHQbKCCWXbgU990QAqDfQ==$0 TESTBIN TABLE 2017-09-03:12:46:32 TEST2 BIN$WELbyCwFaGTgU990QAoh8w==$0 TEST2BIN TABLE 2017-09-03:12:59:27 |
能查看到TEST和TEST2的记录。
在TEST2用户下清理回收站
SQL> show user USER 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 user USER 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 |
2.4.3 表空间级别
表空间级别删除回收站中记录，需要知道表空间，命令如下：
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:32
SQL> 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用户下执行。
3 从回收站中恢复表
从回收站中恢复表用到的命令为flashback table，如下：
flashback tabletestbin to before drop; |
测试如下：
创建对象并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,'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:50 TESTBIN BIN$WEO4ydVjdITgU990QApxdg==$0 TABLE 2017-09-03:14:01:14 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:14:03:50 BIN$WEO4ydVmdITgU990QApxdg==$0 IND_PK_TESTBIN INDEX 2017-09-03:14:03:50 BIN$WEO4ydVjdITgU990QApxdg==$0 TESTBIN TABLE 2017-09-03:14:01:14 BIN$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 tabletestbin 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回收站的基本操作”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！
文章题目：ORACLE回收站的基本操作
当前网址：http://myzitong.com/article/ihdigd.html