SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> !cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel \r on an \m
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> !cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel \r on an \m
回收站就是一个虚拟的容器,用于存储删除的对象。在回收站中,被删除的对象将占有创建时同样的空间
我们可以通过参数 recyclebin来关闭开启回收站功能:
http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5160.htm#REFRN29036
下面不重点介绍Flashback。
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> alter system set recyclebin=off scope=spfile;
系统已更改。
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
创建表diy_os:
SQL> create table diy_os(id int);
表已创建。
SQL> show user;
USER 为 "HR"
SQL> drop table diy_os;
表已删除。
查看已经删除的表diy_os:
我们可以通过参数 recyclebin来关闭开启回收站功能:
http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5160.htm#REFRN29036
下面不重点介绍Flashback。
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
系统已更改。
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
创建表diy_os:
表已创建。
SQL> show user;
USER 为 "HR"
SQL> drop table diy_os;
表已删除。
查看已经删除的表diy_os:
SQL> select * from tab where rownum=1;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$GCvSLxFD5lngUAB/AQA5vg==$0 TABLE
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
DIY_OS BIN$GCvSLxFD5lngUAB/AQA5vg==$0 TABLE 2015-06-10:23:49:32
SQL> select * from dba_recyclebin where original_name='DIY_OS';
OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT SPACE
------------------------------ ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- --- --- ---------- ----------- ------------ ----------
HR BIN$GCvSLxFD5lngUAB/AQA5vg==$0 DIY_OS DROP TABLE USERS 2015-06-10:23:49:16 2015-06-10:23:49:32 9610985 YES YES 78194 78194 78194 0
注意,如果删除的是sys用户的对象,则回收站里无记录,oracle不建议在sys用户里创建对象:
SQL> show user;
USER 为 "SYS"
SQL> create table kernel(id int);
表已创建。
SQL> select * from dba_recyclebin where original_name='KERNEL';
未选定行
使用purge recyclebin可以清除回收站里的所有对象,可以purge user_recyclebin或者purge dba_recyclebin,通过purge tablespace tablespace_name,purge table user.table_name来选择删除
回收站里的对象
还需注意的是,当我们使用drop tablespace.....including contents命令来删除表空间,表空间中的所有对象会被删除,包括回收站里的内容。当使用drop user .....cascade命令来删除用户时,该用户
下的所有对象会被删除,包括回收站里的内容
SQL> purge table hr.diy_os;
表已清除。
SQL> select * from dba_recyclebin where original_name='DIY_OS';
未选定行
下面看下执行计划:
SQL> select * from dba_recyclebin;
执行计划
----------------------------------------------------------
Plan hash value: 1935272164
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 2 | 336 | 12 (
9)| 00:00:01 |
|* 1 | FILTER | | | |
| |
|* 2 | HASH JOIN | | 2 | 336 | 12 (
9)| 00:00:01 |
| 3 | NESTED LOOPS | | | |
| |
| 4 | NESTED LOOPS | | 2 | 292 | 10 (
0)| 00:00:01 |
| 5 | NESTED LOOPS | | 2 | 202 | 6 (
0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 2 | 168 | 4 (
0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | RECYCLEBIN$ | 2 | 146 | 2 (
0)| 00:00:01 |
| 8 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | 1 (
0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (
0)| 00:00:01 |
| 10 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (
0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (
0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (
0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 45 | 2 (
0)| 00:00:01 |
| 14 | INDEX FULL SCAN | I_USER2 | 97 | 2134 | 1 (
0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 29 | 2 (
0)| 00:00:01 |
|* 16 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (
0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
下面摘自?/rdbms/admin/dsqlddl.bsq
create table recyclebin$
(
obj# number not null, /* original object number */
owner# number not null, /* owner user number */
original_name varchar2(32), /* Original Object Name */
operation number not null, /* Operation carried out */
/* 0 -> DROP */
/* 1 -> TRUNCATE (not supported) */
type# number not null, /* object type (see KQD.H) */
ts# number, /* tablespace number */
file# number, /* segment header file number */
block# number, /* segment header block number */
droptime date, /* time when object was dropped */
dropscn number, /* SCN of Tx which caused the drop */
partition_name varchar2(32), /* Name of the partition dropped */
/* NULL otherwise */
flags number, /* flags for undrop processing */
related number not null, /* obj one level up in heirarchy */
bo number not null, /* base object */
purgeobj number not null, /* obj to purge when purging this */
base_ts# number, /* Base objects Tablespace number */
base_owner# number, /* Base objects owner number */
space number, /* number of blocks used by the object */
con# number, /* con#, if index is due to constraint */
spare1 number,
spare2 number,
spare3 number
)
/
create index recyclebin$_obj on recyclebin$(obj#)
/
create index recyclebin$_ts on recyclebin$(ts#)
/
create index recyclebin$_owner on recyclebin$(owner#)
/
上面是数据字典recyclebin$的创建和注释,不断的深入,会更加清楚的认知。
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$GCvSLxFD5lngUAB/AQA5vg==$0 TABLE
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
DIY_OS BIN$GCvSLxFD5lngUAB/AQA5vg==$0 TABLE 2015-06-10:23:49:32
SQL> select * from dba_recyclebin where original_name='DIY_OS';
OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT SPACE
------------------------------ ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- --- --- ---------- ----------- ------------ ----------
HR BIN$GCvSLxFD5lngUAB/AQA5vg==$0 DIY_OS DROP TABLE USERS 2015-06-10:23:49:16 2015-06-10:23:49:32 9610985 YES YES 78194 78194 78194 0
注意,如果删除的是sys用户的对象,则回收站里无记录,oracle不建议在sys用户里创建对象:
SQL> show user;
USER 为 "SYS"
SQL> create table kernel(id int);
表已创建。
SQL> drop table kernel;
表已删除。
SQL> select * from dba_recyclebin where original_name='KERNEL';
未选定行
回收站里的对象
还需注意的是,当我们使用drop tablespace.....including contents命令来删除表空间,表空间中的所有对象会被删除,包括回收站里的内容。当使用drop user .....cascade命令来删除用户时,该用户
下的所有对象会被删除,包括回收站里的内容
SQL> purge table hr.diy_os;
表已清除。
SQL> select * from dba_recyclebin where original_name='DIY_OS';
未选定行
下面看下执行计划:
SQL> select * from dba_recyclebin;
执行计划
----------------------------------------------------------
Plan hash value: 1935272164
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 2 | 336 | 12 (
9)| 00:00:01 |
|* 1 | FILTER | | | |
| |
|* 2 | HASH JOIN | | 2 | 336 | 12 (
9)| 00:00:01 |
| 3 | NESTED LOOPS | | | |
| |
| 4 | NESTED LOOPS | | 2 | 292 | 10 (
0)| 00:00:01 |
| 5 | NESTED LOOPS | | 2 | 202 | 6 (
0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 2 | 168 | 4 (
0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | RECYCLEBIN$ | 2 | 146 | 2 (
0)| 00:00:01 |
| 8 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | 1 (
0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (
0)| 00:00:01 |
| 10 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (
0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (
0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (
0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 45 | 2 (
0)| 00:00:01 |
| 14 | INDEX FULL SCAN | I_USER2 | 97 | 2134 | 1 (
0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 29 | 2 (
0)| 00:00:01 |
|* 16 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (
0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
下面摘自?/rdbms/admin/dsqlddl.bsq
create table recyclebin$
(
obj# number not null, /* original object number */
owner# number not null, /* owner user number */
original_name varchar2(32), /* Original Object Name */
operation number not null, /* Operation carried out */
/* 0 -> DROP */
/* 1 -> TRUNCATE (not supported) */
type# number not null, /* object type (see KQD.H) */
ts# number, /* tablespace number */
file# number, /* segment header file number */
block# number, /* segment header block number */
droptime date, /* time when object was dropped */
dropscn number, /* SCN of Tx which caused the drop */
partition_name varchar2(32), /* Name of the partition dropped */
/* NULL otherwise */
flags number, /* flags for undrop processing */
related number not null, /* obj one level up in heirarchy */
bo number not null, /* base object */
purgeobj number not null, /* obj to purge when purging this */
base_ts# number, /* Base objects Tablespace number */
base_owner# number, /* Base objects owner number */
space number, /* number of blocks used by the object */
con# number, /* con#, if index is due to constraint */
spare1 number,
spare2 number,
spare3 number
)
/
create index recyclebin$_obj on recyclebin$(obj#)
/
create index recyclebin$_ts on recyclebin$(ts#)
/
create index recyclebin$_owner on recyclebin$(owner#)
/
上面是数据字典recyclebin$的创建和注释,不断的深入,会更加清楚的认知。