Oracle回收站概念及功能

这篇文章主要介绍“Oracle回收站概念及功能”，在日常操作中，相信很多人在Oracle回收站概念及功能问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Oracle回收站概念及功能”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
目录 一、回收站概念 二、回收站功能 三、管理回收站 四、示例 1、先后删除的表名相同，然后闪回表的问题 2、Flashback
Drop只能用于非系统表空间和本地管理的表空间
3、理解重命名的过程
4、删除表与闪回删除表对索引和约束的影响 5、当表空间不足时，无法闪回表删除的问题 11g官方文档对RecycleBin的解释说明 一、回收站概念 从ORACLE
10g开始，引入了回收站(Recycle Bin)的概念。它的全称叫Tablespace
Recycle Bin。回收站实际是一个逻辑容器（逻辑区域），原理有点类似于WINDOW系统的回收站。它以表空间中现有已经分配的空间为基础，而不是从表空间上物理划出一个固定区域用作回收站。这意味着回收站和表空间中的对象共用存储区域、系统没有给回收站预留空间。因此，当表被DROP后，如果可用空间充足，并且没有对回收站进行清理，那么被DROP掉的对象会一直存在回收站中，但是如果可用空间紧张的情况下，数据库会根据先进先出的顺序覆盖Recycle
Bin中的对象。所以回收站机制也不是百分百的保险机制。另外从原理上来说它就是一个数据字典表，放置用户Drop掉的数据库对象信息。用户进行Drop操作的对象并没有真正被数据库删除，仍然会占用空间。除非是由于用户手工进行Purge或者因为存储空间不够而被数据库清掉。数据库有了这样的功能，能够减少很多不必要的麻烦。当用户、开发人员、甚至DBA误操作删除了表，那么我们不必还原整个数据库或表空间，直接使用ORACLE
10g的闪回(FLASHBACK,闪回)功能来还原被删除的表。这样我们就能避免大量的人工误操作。这是一个对DBA相当有用的功能。Flashback Drop是基于Tablespace
RecycleBin来实现恢复的。它只支持闪回与table相关连的对象，比如表，索引，约束，触发器等。如果是函数或者存储过程等，就需要使用Flashback
Query来实现。二、回收站功能 回收站这个特性主要的好处就是在误删除一个表时有一个恢复机制，不必通过数据库还原来实现。避免大量的人工误操作。以及数据库还原等复杂的操作。让数据库的管理、维护更加简单、方便。如果是SQL
SERVER数据库，就必须还原整个数据库来找到被DROP掉的表。可见回收站功能确实是一个开创性的功能。三、管理回收站 1、开启、关闭回收站 首先你可以通过命令查看数据库是否开启了回收站机制， 如下所示
VALUE= ON表示开启了回收站机制。OFF则表示回收站机制关闭。SYS@seiang11g>show parameter
recyclebin NAME TYPE VALUE————————————
———– ——————————recyclebin string on或者 SYS@seiang11g>select name,value
from v$parameter where name=’recyclebin’; NAME VALUE——————————
——————–recyclebin on可以通过设置初始化参数recyclebin启用或禁用回收站功能。当然也可以用命令关闭回收站SYS@seiang11g>alter system set
recyclebin=off scope=spfile; System
altered.SYS@seiang11g>alter session set
recyclebin=off; Session
altered.SYS@seiang11g>show parameter
recyclebin NAME TYPE VALUE————————————
———– ——————————recyclebin string OFF可以用命令开启回收站SYS@seiang11g>alter system set
recyclebin=on scope=spfile; System
altered.SYS@seiang11g>alter session set
recyclebin=on; Session
altered.SYS@seiang11g>show parameter
recyclebin NAME TYPE VALUE————————————
———– ——————————recyclebin string ON查看回收站对象先来看一个例子，如下所示，假如不小心误操作DROP了表wjq1，然后在回收站查看被DROP的表对象SYS@seiang11g>select
owner,table_name,tablespace_name from dba_tables where owner=’SEIANG’; OWNER TABLE_NAME TABLESPACE_NAME——————————
—————————— ——————————SEIANG WJQ1 SEIANGSYS@seiang11g>drop table
seiang.wjq1; Table
dropped.SEIANG@seiang11g>show recyclebin ORIGINAL
NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME—————-
—————————— ———— ——————-WJQ1 BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0
TABLE 2017-08-18:14:35:07 其中RECYCLEBIN是USER_RECYCLEBIN
的同义词。SEIANG@seiang11g>select
object_name,original_name,operation,type,droptime,ts_name from recyclebin; OBJECT_NAME ORIGINAL_N OPERATION
TYPE DROPTIME TS_NAME——————————
———- ——— ————————- ——————-
—————BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0
WJQ1 DROP TABLE 2017-08-18:14:35:07 SEIANG 查看数据库当前用户的回收站对象SQL> SELECT * FROM
USER_RECYCLEBIN; –查看数据库回收站所有对象SQL> SELECT * FROM
DBA_RECYCLEBIN; 四、示例 1、先后删除的表名相同，然后闪回表的问题 为了避免被删除的表与同类对象名称的重复，被删除的表以及相依的对象放到回收站后，ORACLE数据库会对被删除的对象名称进行重命名，例如表wjq1表
SEIANG@seiang11g>show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————-
—————————— ———— ——————-
WJQ1
BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0 TABLE 2017-08-18:14:35:07

我们又创建了表wjq1，然后删除了该表wjq1，如下所示，虽然ORIGINAL_NAME一致，但是RECYCLEBIN
NAME则有所不同。
SEIANG@seiang11g>create table wjq1(name varchar2(20),address varchar2(20))
tablespace good;
Table
created.

SEIANG@seiang11g>insert into wjq1
values(‘wjq’,’beijing’);
1 row
created.

SEIANG@seiang11g>insert into wjq1
values(‘seiang’,’lanzhou’);
1 row
created.

SEIANG@seiang11g>commit;
Commit
complete.

SEIANG@seiang11g>drop table wjq1;
Table
dropped.

SEIANG@seiang11g>show recyclebin
ORIGINAL
NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————-
—————————— ———— ——————-
WJQ1
BIN$VwJ/MWH8dIfgUy4BAQq2eQ==$0 TABLE 2017-08-18:14:47:04
WJQ1
BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0 TABLE 2017-08-18:14:35:07

SEIANG@seiang11g>select * from
“BIN$VwJ/MWH8dIfgUy4BAQq2eQ==$0”;

NAME ADDRESS
——————–
——————–
wjq beijing
seiang lanzhou

SEIANG@seiang11g>select * from
“BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0”;

ID NAME
———-
——————————
1 wjq1
2 wjq2
3 wjq3

还原回收站对象
还原回收站被删除的表、索引等对象， 是通过Flashback
Drop实现的。如下所示。
SEIANG@seiang11g>flashback table
wjq1 to before drop;
Flashback
complete.

SEIANG@seiang11g>select * from wjq1;

NAME ADDRESS
——————–
——————–
wjq beijing
seiang lanzhou

SEIANG@seiang11g>show recyclebin
ORIGINAL
NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————-
—————————— ———— ——————-
WJQ1 BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0
TABLE 2017-08-18:14:35:07

如上所示，如果两个相同名字的表wjq1被删除了，此时闪回被DROP的表wjq1，实质是闪回最后一个被删除的表（后进先出原则）,如果此时继续闪回操作就会报ORA-38312错误

SEIANG@seiang11g>flashback table
wjq1 to before drop;
flashback
table wjq1 to before drop
*
ERROR
at line 1:
ORA-38312: original name is used
by an existing object

此时可以在闪回过程中对表名进行重命名解决问题。。
SEIANG@seiang11g>flashback table wjq1 to before drop rename to wjq1_test;
Flashback complete.

SEIANG@seiang11g>select * from
wjq1_test;

ID NAME
———-
——————————
1 wjq1
2 wjq2
3 wjq3

当再次查看回收站中的内容时，发现已经没有了。

另外，如果回收站有两个被DROP掉的表wjq1,
如果想闪回第一个被删除的表，那该怎么办呢？
SEIANG@seiang11g>show recyclebin
ORIGINAL
NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————-
—————————— ———— ——————-
WJQ1 BIN$VwJ/MWH+dIfgUy4BAQq2eQ==$0 TABLE 2017-08-18:14:59:11
WJQ1 BIN$VwJ/MWH9dIfgUy4BAQq2eQ==$0
TABLE 2017-08-18:14:58:05

其实这个也很好处理,直接指定RECYCLEBIN
NAME进行闪回即可。
SEIANG@seiang11g>flashback table “BIN$VwJ/MWH9dIfgUy4BAQq2eQ==$0” to
before drop;
Flashback complete.

SEIANG@seiang11g>select * from wjq1;

NAME ADDRESS
——————–
——————–
wjq beijing
seiang lanzhou清空回收站 数据库对象删除后，数据库会把它重命名为BIN$开头的对象，你可以通过ORIGINAL_NAME查看它对应的原始对象名称。记住，将表放在回收站里并不在原始表空间中释放空间。如果您希望完全删除该表，而不让该表放入回收站，可以使用以下命令永久删除该表。当然这样操作后，你也不能通过使用闪回特性闪回该表了。DROP TABLE [TABLE_NAME] PURGE;如果数据库中删除表时都放入回收站，因而没有释放所占空间，那么当空闲的空间不足时，已经删除的表是否还会侵占存储空间呢?答案很简单：当表空间被回收站数据完全占满，以至于必须扩展数据文件来容纳更多数据时，可以说表空间处于“空间压力”情况下。此时，对象以先进先出的方式从回收站中自动清除。在删除表之前，相关对象(如索引)被删除。同样，空间压力可能由特定表空间定义的用户限额而引起。表空间可能有足够的空余空间，但用户可能将其在该表空间中所分配的部分用完了。在这种情况下，Oracle
自动清除该表空间中属于该用户的对象。此外，有几种方法可以手动控制回收站。如果在删除名为
TEST 的特定表之后需要从回收站中清除它，可以执行PURGE TABLE [TABLE_NAME];或者使用其回收站中的名称：PURGE TABLE “BIN$VwJ/MWH9dIfgUy4BAQq2eQ==$0”;此命令将从回收站中删除表
TEST 及所有相关对象，如索引、约束等，从而节省了空间。但是，如果要从回收站中永久删除索引，则可以使用以下命令来完成工作：PURGE INDEX [INDEX_NAME];此命令将仅仅删除索引，而将表的拷贝留在回收站中。有时在更高级别上进行清除可能会有用。例如，您可能希望清除表空间
USERS 的回收站中的所有对象。可以执行：PURGE TABLESPACE
USERS;您也许希望只为该表空间中特定用户清空回收站。在数据仓库类型的环境中，用户创建和删除许多临时表，此时这种方法可能会有用。您可以更改上述命令，限定只清除特定的用户：PURGE TABLESPACE
USERS USER SCOTT;要释放整个回收站占用的空间，您需要使用以下命令清空回收站：PURGE RECYCLEBIN;记住PURGE
RECYCLEBIN只是清除当前用户回收站中的对象，DBA_RECYCLEBIN下的的对象并没有删除，如果你要清除当前数据库回收站的对象，必须使用下面命令（DBA权限）PURGE
DBA_RECYCLEBIN2、Flashback
Drop只能用于非系统表空间和本地管理的表空间
—创建的sys_table表属于非系统表空间SEIANG
SYS@seiang11g>create table
sys_table(id number,idcard number) tablespace seiang;
Table created.
SYS@seiang11g>insert into sys_table
values(1,123456);
1 row
created.

SYS@seiang11g>commit;
Commit
complete.

SYS@seiang11g>select owner,table_name,tablespace_name
from dba_tables where table_name=’SYS_TABLE’;

OWNER TABLE_NAME TABLESPACE_NAME
——————————
—————————— ——————————
SYS SYS_TABLE SEIANG

SYS@seiang11g>drop table
sys_table;
Table
dropped.

SYS@seiang11g>show recyclebin
ORIGINAL
NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————-
—————————— ———— ——————-
SYS_TABLE BIN$VwJUfr+DbQDgUy4BAQqy8Q==$0
TABLE 2017-08-18:15:08:17

—创建的sys_table1表属于系统表空间SYSTEM
SYS@seiang11g>create table
sys_table1(id number,name varchar2(20));
Table created.

SYS@seiang11g>insert into
sys_table1 values(1,’www’);
1 row
created.

SYS@seiang11g>commit;
Commit
complete.

SYS@seiang11g>select
owner,table_name,tablespace_name from dba_tables where table_name=’SYS_TABLE1′;

OWNER TABLE_NAME TABLESPACE_NAME
——————————
—————————— ——————————
SYS SYS_TABLE1 SYSTEM

SYS@seiang11g>drop table
sys_table1;
Table
dropped.

SYS@seiang11g>show recyclebin

由上面的例子可以发现，在系统表空间中，表对象删除后就真的从系统中删除了，而不是存放在回收站中。 3、重命名的过程 —创建基于emp表的tab_emp表
SCOTT@seiang11g>create table
tab_emp as select * from emp;
Table
created.

—添加主键约束，将自动产生主键索引
SCOTT@seiang11g>alter table tab_emp
add constraint pk_empno primary key(empno);
Table
altered.

—添加唯一键约束，将自动产生唯一索引
SCOTT@seiang11g>alter table tab_emp
add constraint uk_ename unique(ename);
Table
altered.

—添加check约束
SCOTT@seiang11g>alter table tab_emp
add constraint ck_sal check(sal>0);
Table
altered.

—添加非空约束
SCOTT@seiang11g>alter table tab_emp
modify job constraint nn_job not null;
Table
altered.

—添加外键约束
SCOTT@seiang11g>alter table tab_emp
add constraint fk_dept foreign key(deptno) references dept(deptno) on delete
cascade;
Table
altered.

—查看tab_emp表上的所有约束和索引
SCOTT@seiang11g>select
OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints
2 where table_name=’TAB_EMPLOYEE’
3 union all
4 select
TABLE_OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes
5 where table_name=’TAB_EMPLOYEE’;

OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME
———-
——————– ——————– ——————————
SCOTT PK_EMPNO P TAB_EMPLOYEE
SCOTT UK_ENAME U TAB_EMPLOYEE
SCOTT CK_SAL C TAB_EMPLOYEE
SCOTT NN_JOB C TAB_EMPLOYEE
SCOTT FK_DEPT R TAB_EMPLOYEE
SCOTT UK_ENAME NORMAL TAB_EMPLOYEE
SCOTT PK_EMPNO NORMAL TAB_EMPLOYEE

—查看tab_emp表所在文件的id,块的起始id,大小
SYS@seiang11g>select
file_id,block_id,bytes from dba_extents where segment_name=’TAB_EMP’;

FILE_ID BLOCK_ID
BYTES
———-
———- ———-
4 528
65536

—查看tab_emp表对象的id
SYS@seiang11g>select
object_name,object_id from dba_objects where object_name=’TAB_EMP’;

OBJECT_NAME OBJECT_ID
——————————
———-
TAB_EMP 89445

—对tab_emp表进行重命名为tab_employee
SCOTT@seiang11g>alter table tab_emp
rename to tab_employee;
Table
altered.

—查看重命名表tab_employee所在文件的id,块的起始id,大小，发现没有发生任何变化
SYS@seiang11g>select
file_id,block_id,bytes from dba_extents where segment_name=’TAB_EMPLOYEE’;

FILE_ID
BLOCK_ID BYTES
———-
———- ———-
4 528
65536

—查看重命名表tab_employee对象的id，发现没有发生任何的变化
SYS@seiang11g>select object_name,object_id
from dba_objects where object_name=’TAB_EMPLOYEE’;

OBJECT_NAME OBJECT_ID
——————————
———-
TAB_EMPLOYEE 89445

—查看重命名表tab_emp上的所有约束和索引，发现没有发生任何变化
SCOTT@seiang11g>select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME
from user_constraints
2 where table_name=’TAB_EMPLOYEE’
3 union all
4 select
TABLE_OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes
5 where
table_name=’TAB_EMPLOYEE’;

OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME
———-
——————– ——————– ——————————
SCOTT PK_EMPNO P TAB_EMPLOYEE
SCOTT UK_ENAME U TAB_EMPLOYEE
SCOTT CK_SAL C TAB_EMPLOYEE
SCOTT NN_JOB C TAB_EMPLOYEE
SCOTT FK_DEPT R TAB_EMPLOYEE
SCOTT UK_ENAME NORMAL TAB_EMPLOYEE
SCOTT PK_EMPNO NORMAL TAB_EMPLOYEE

从上面的演示可以看出对于表的重命名仅仅是修改了表名，而对于表对象的ID，以及表存放的位置，块的起始，大小等并未发生实质性的变化。 4、删除表与闪回删除表对索引和约束的影响
—删除tab_employee表，并查看回收站
SCOTT@seiang11g>drop table
tab_employee;
Table
dropped.

SCOTT@seiang11g>show recyclebin
ORIGINAL
NAME RECYCLEBIN NAME OBJECT TYPE DROP T
—————————————————————————————————————————–
TAB_EMPLOYEE BIN$VwRUrQdoe8vgUy4BAQoEBw==$0 TABLE 2017-08-18:16:58:20

SCOTT@seiang11g>select
object_name,original_name,can_undrop,base_object from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME CAN BASE_OBJECT
——————————
——————————– — ———–
BIN$VwRUrQdme8vgUy4BAQoEBw==$0
PK_EMPNO NO 89445
BIN$VwRUrQdne8vgUy4BAQoEBw==$0
UK_ENAME NO 89445
BIN$VwRUrQdoe8vgUy4BAQoEBw==$0
TAB_EMPLOYEE YES 89445

— tab_employee表无法进行查询
SCOTT@seiang11g>select count(*)
from tab_employee;
select
count(*) from tab_employee
*
ERROR
at line 1:
ORA-00942: table or view does not exist

— 删除tab_employee表后，可以通过回收站的名来进行查看
SCOTT@seiang11g>select count(*)
from “BIN$VwRUrQdoe8vgUy4BAQoEBw==$0”;

COUNT(*)
———-
14

— 查看tab_employee表上的所有约束和索引
SCOTT@seiang11g>select
OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints
2 where table_name=’TAB_EMPLOYEE’;
no rows
selected

SCOTT@seiang11g>
SCOTT@seiang11g>select
TABLE_OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes
2 where
table_name=’TAB_EMPLOYEE’;
no rows
selected

— 从回收站中闪回删除的tab_employee表
SCOTT@seiang11g>flashback table tab_employee to before drop;
Flashback complete.

— 闪回后表存在，并且可以进行访问
SCOTT@seiang11g>select count(*) from tab_employee;
COUNT(*)
———-
14

—删除后查看约束和索引的名称，仍然是回收站的名称BIN$，同时发现外键约束消失
SCOTT@seiang11g>select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME
from user_constraints
2 where
table_name=’TAB_EMPLOYEE’
3 union all
4 select
TABLE_OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes
5 where table_name=’TAB_EMPLOYEE’;

OWNER
CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME
———- ————————————————–
——————– ——————————
SCOTT
BIN$VwRUrQdie8vgUy4BAQoEBw==$0
P TAB_EMPLOYEE
SCOTT
BIN$VwRUrQdje8vgUy4BAQoEBw==$0 U TAB_EMPLOYEE
SCOTT
BIN$VwRUrQdke8vgUy4BAQoEBw==$0 C TAB_EMPLOYEE
SCOTT
BIN$VwRUrQdle8vgUy4BAQoEBw==$0 C TAB_EMPLOYEE
SCOTT
BIN$VwRUrQdne8vgUy4BAQoEBw==$0 NORMAL TAB_EMPLOYEE
SCOTT
BIN$VwRUrQdme8vgUy4BAQoEBw==$0 NORMAL TAB_EMPLOYEE

从上面的查询可以看出闪回之后索引、约束的名字还是使用了以BIN$开头，由系统生成的名字，可以将其改回，但外键约束已经不存在了

尝试对表进行DML操作

—插入数据，发现可以成功插入，deptno列的外键约束已经被删除，故deptno为70号成功插入
SCOTT@seiang11g>insert into
tab_employee(empno,ename,job,sal,deptno) select 6666,’seiang’,’DBA’,5000,70
from dual;
1 row
created.

—将BIN$开头的索引、约束改回原来的名字
SCOTT@seiang11g>alter index
“BIN$VwRUrQdme8vgUy4BAQoEBw==$0” rename to pk_empno;
Index
altered.

SCOTT@seiang11g>alter index
“BIN$VwRUrQdne8vgUy4BAQoEBw==$0” rename to UK_ENAME;
Index
altered.

SCOTT@seiang11g>alter table
tab_employee rename constraint “BIN$VwRUrQdle8vgUy4BAQoEBw==$0” to
NN_JOB;
Table
altered.

SCOTT@seiang11g>alter table
tab_employee rename constraint “BIN$VwRUrQdke8vgUy4BAQoEBw==$0” to
ck_sal;
Table
altered.

SCOTT@seiang11g>alter table tab_employee
rename constraint “BIN$VwRUrQdje8vgUy4BAQoEBw==$0” to uk_ename;
Table
altered.

SCOTT@seiang11g>alter table
tab_employee rename constraint “BIN$VwRUrQdie8vgUy4BAQoEBw==$0” to
pk_empno;
Table
altered.5、当表空间不足时，无法闪回表删除的问题 —创建一个tab_test表空间，大小为1M,并且不能自动扩展
SYS@seiang11g>create tablespace
tab_test datafile ‘/u01/app/oracle/oradata/OraDB11g/tab_test01.dbf’ size 1M;
Tablespace
created.

—查看tab_test表空间不是自动扩展
SYS@seiang11g>select
t.tablespace_name,d.file_name,d.autoextensible
2 from dba_tablespaces t,dba_data_files
d
3 where
t.tablespace_name=d.tablespace_name;

TABLESPACE_NAME FILE_NAME AUT
——————————
————————————————– —
USERS /u01/app/oracle/oradata/OraDB11g/users01.dbf YES
UNDOTBS1
/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf YES
SYSAUX
/u01/app/oracle/oradata/OraDB11g/sysaux01.dbf YES
SYSTEM /u01/app/oracle/oradata/OraDB11g/system01.dbf YES
EXAMPLE
/u01/app/oracle/oradata/OraDB11g/example01.dbf YES
SEIANG
/u01/app/oracle/oradata/OraDB11g/seiang01.dbf NO
GOOD /u01/app/oracle/oradata/OraDB11g/good01.dbf NO
TAB_TEST
/u01/app/oracle/oradata/OraDB11g/tab_test01.dbf NO


—查看tab_test表空间的空闲空间
SYS@seiang11g>select
tablespace_name,sum(bytes/1024/1024) ||’ M’
2 from dba_free_space where
tablespace_name=’TAB_TEST’ group by tablespace_name;

TABLESPACE_NAME SUM(BYTES/1024/1024)||’M’
——————————
——————————————
TAB_TEST .9375 M

—在tab_test表空间上创建一张test表，隶属于scott用户，创建的同时并插入数据
SYS@seiang11g>create table
scott.test tablespace tab_test as select * from dba_objects where
rownumTable
created.
–插入数据后查看tab_test表空间的空闲空间
SYS@seiang11g>select
tablespace_name,sum(bytes/1024/1024) ||’ M’
2 from dba_free_space where
tablespace_name=’TAB_TEST’ group by tablespace_name;

TABLESPACE_NAME SUM(BYTES/1024/1024)||’M’
——————————
——————————————
TAB_TEST .25 M

—删除test表，但是不purge
SYS@seiang11g>drop table
scott.test;
Table
dropped.

—删除test表之后，查看tab_test表空间的空闲大小，为1M，但并不是真正的1M,在需要表空间时,将自动清除回收站最老的对象,以满足当前空间需求
SYS@seiang11g>select
tablespace_name,sum(bytes/1024/1024) ||’ M’
2 from dba_free_space where tablespace_name=’TAB_TEST’
group by tablespace_name;

TABLESPACE_NAME SUM(BYTES/1024/1024)||’M’
——————————
——————————————
TAB_TEST .9375 M

—查看回收站的信息，删除的test表对象位于回收站中
SCOTT@seiang11g>show recyclebin
ORIGINAL
NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————-
—————————— ———— ——————-
TEST BIN$VwJUfr+EbQDgUy4BAQqy8Q==$0
TABLE 2017-08-18:17:35:33

—然后，创建test2表，隶属于tab_test表空间
SYS@seiang11g>create table test2
tablespace tab_test as select * from dba_objects where rownumTable
created.

—创建完成后，查看回收站的信息，此时回收站中原来的表test的记录被自动清除
SCOTT@seiang11g>show recyclebin
SCOTT@seiang11g>select object_name,original_name,can_undrop,base_object
from user_recyclebin;
no rows
selected

—此时test表不能够被闪回
SCOTT@seiang11g>flashback table test to before drop;
flashback table test to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN总结：
1、表的删除被映射为将表的重命名，然后将其置于回收站。
2、表的索引,触发器,授权闪回后将不受到影响.索引,触发器名字可以根据需要进行更改回原来名称。
3、对于约束，如果是外键约束，表删除之后将不可恢复，其余的约束不受影响。
4、当删除表时，依赖于该表的物化视图也会同时删除，但是由于物化视图并不会放入recycle
bin中，因此当你执行flashback drop时，并不能恢复依赖其的物化视图。需要DBA手工重建。
5、如果要查询回收站中的对象，建议将对象名使用双引号括起来。
6、对于回收站（Recycle
Bin）中的对象，只支持查询。不支持任何其他DML、DDL等操作。
7、闪回的实质并不能撤销已提交的事务，而是构造倒退原有事务影响的另一个事务。
8、对于已经删除的表如果在所在的表空间新增对象由于空间不足的压力而被重用将导致闪回失败。
9、对于表空间不足时，系统会自动清除回收站中最老的对象，以满足当前需求，即采用FIFO原则。
10、闪回表的常用方法
flashback
table tbname to before drop ;
flashback
table [tbname] to before drop rename to [newtbname];
第二条语句用于被删除的表名已经被再次重用,故闪回之前必须将其改名为新表名,schema不变化
11、如回收站中存在两个相同的原表名，则闪回时总是闪回最近的版本，如果闪回特定的表，需要指定该表在回收站中的名称。如
flashback
table “BIN$k1zC3yEiwZvgQAB/AQBRVw==$0” to before drop;
12、lashback
drop不能闪回truncate命令截断的表，而是只能恢复drop之后的表
13、flashback
drop不能闪回drop
user scott cascade删除方案的操作,此只能用flashback
database
14、在system表空间中存储的表无法启用flashback
drop，且这些表会被立即删除以下是11g官方文档对于recyclebin的说明：
********************************************************************************Using Flashback Drop and Managing the
Recycle Bin

When you drop a
table, the database does not immediately remove the space associated with the
table. The database renames the table and places it and any associated objects
in a recycle bin, where, in case the table was dropped in error, it can be
recovered at a later time. This feature is calledFlashback
Drop, and theFLASHBACKTABLEstatement is used to restore the
table. Before discussing the use of theFLASHBACKTABLEstatement for this purpose, it is
important to understand how the recycle bin works, and how you manage its
contents.
This section
contains the following topics:
What Is the Recycle Bin?
Viewing and Querying Objects in the
Recycle Bin
Purging Objects in the Recycle Bin
Restoring Tables from the Recycle
Bin What Is the Recycle Bin?
Therecycle bin is actually a data
dictionary table containing information about dropped objects. Dropped tables and any associated objects such as
indexes, constraints, nested tables, and the likes are not removed and still
occupy space. They continue to count against user space quotas, until specifically
purged from the recycle bin or the unlikely situation where they must be purged
by the database because of tablespace space constraints.回收站实际上是一个数据字典表，其中包含关于删除对象的信息。 删除的表和其任何关联的对象（如索引，约束，嵌套表等）都不会被删除，仍然占用空间。 他们继续计算用户空间配额，直到从回收站特别清除，或者由于表空间空间限制，数据库必须清除它们的不太可能的情况。
Each user can be
thought of as having his own recycle bin, because, unless a user has theSYSDBAprivilege, the only objects that
the user has access to in the recycle bin are those that the user owns. A user
can view his objects in the recycle bin using the following statement:每个用户可以被认为拥有自己的回收站，因为除非用户具有SYSDBA权限，否则用户在回收站中访问的唯一对象是用户拥有的对象。
SELECT * FROM
RECYCLEBIN;
When you drop a
tablespace including its contents, the objects in the tablespace are not placed
in the recycle bin and the database purges any entries in the recycle bin for
objects located in the tablespace. The database also purges any recycle bin
entries for objects in a tablespace when you drop the tablespace, not including
contents, and the tablespace is otherwise empty. Likewise:当你删除包含其内容的表空间时，表空间中的对象不会放置在回收站中，数据库会清除回收站中位于表空间中的对象的任何条目。 当您删除表空间（不包括内容）时，数据库还会清除表空间中对象的任何回收站条目，否则表空间为空。同样：
When you drop a user, any
objects belonging to the user are not placed in the recycle bin and any
objects in the recycle bin are purged.
When you drop a cluster,
its member tables are not placed in the recycle bin and any former member
tables in the recycle bin are purged.
When you drop a type, any
dependent objects such as subtypes are not placed in the recycle bin and
any former dependent objects in the recycle bin are purged.
当你删除用户时，属于用户的任何对象都不会放置在回收站中，并且清理回收站中的任何对象。 当你删除集群时，其成员表不会放置在回收站中，并且清除回收站中的任何以前的成员表。 当你删除类型时，任何依赖对象（如子类型）都不会放置在回收站中，并且清除回收站中的任何以前的依赖对象。 Object Naming in the Recycle Bin
When a dropped
table is moved to therecycle bin, the table and
its associated objects are given system-generated names. This is necessary to
avoid name conflicts that may arise if multiple tables have the same name. This
could occur under the following circumstances:当删除的表移动到回收站时，表及其关联的对象将被给予系统生成的名称。 这是必要的，以避免在多个表具有相同名称时可能出现的名称冲突。 这可能发生在以下情况下：
A user drops a table,
re-creates it with the same name, then drops it again.
Two users have tables
with the same name, and both users drop their tables.
用户删除表，重新创建一个表，然后再次删除它。 两个用户具有相同名称的表，两个用户都删除它们的表。
The renaming
免费云主机域名
convention is as follows:
BIN$unique_id$version
where:unique_idis a 26-character globally unique
identifier for this object, which makes the recycle bin name unique across
all databases versionis a version number assigned by the
database
unique_id是此对象的26个字符的全局唯一标识符，这使得回收站名称在所有数据库中都是唯一的 version是由数据库分配的版本号 Enabling and Disabling the Recycle Bin
When the recycle
bin is enabled, dropped tables and their dependent objects are placed in the
recycle bin. When the recycle bin is disabled, dropped tables and their
dependent objects arenotplaced in the recycle bin; they are
just dropped, and you must use other means to recover them (such as recovering
from backup).当启用回收站时，丢弃的表及其相关对象将被放置在回收站中。 当回收站被禁用时，丢弃的表及其相关对象不会放置在回收站中; 它们刚刚被删除，您必须使用其他方法来恢复它们（例如从备份恢复）。
Disabling the
recycle bin does not purge or otherwise affect objects already in the recycle
bin. The recycle bin is enabled by default.禁用回收站不会清除或影响已经在回收站中的对象。 默认情况下启用回收站。
You enable and
disable the recycle bin by changing therecyclebininitialization parameter. This
parameter is not dynamic, so a database restart is required when you change it
with anALTERSYSTEMstatement.通过更改recyclebin初始化参数启用和禁用回收站。 此参数不是动态的，因此使用ALTER SYSTEM语句更改数据库时，需要重新启动数据库。 To disable the recycle
bin:
Issue
one of the following statements:
2. ALTER SESSION SET recyclebin = OFF;
3.
4. ALTER SYSTEM SET recyclebin = OFF SCOPE
= SPFILE;
If
you usedALTERSYSTEM, restart the database. To enable the
recycle bin:
Issue
one of the following statements:
2. ALTER SESSION SET recyclebin = ON;
3.
4. ALTER SYSTEM SET recyclebin = ON SCOPE =
SPFILE;
If
you usedALTERSYSTEM, restart the database. Viewing and Querying Objects in the
Recycle Bin
Oracle Database
provides two views for obtaining information about objects in therecycle bin:View
Description
USER_RECYCLEBIN This view can be used by users to see their own dropped
objects in the recycle bin. It has a synonymRECYCLEBIN, for
ease of use. 用户可以使用此视图在回收站中查看自己删除的对象。
它具有同义词RECYCLEBIN，方便使用。
DBA_RECYCLEBIN This view gives administrators visibility to all
dropped objects in the recycle bin 此视图使管理员可以看到回收站中的所有已删除对象

One use for
these views is to identify the name that the database has assigned to a dropped
object, as shown in the following example:
SELECT
object_name, original_name FROM dba_recyclebin
WHERE owner = ‘HR’;
OBJECT_NAME ORIGINAL_NAME
——————————
——————————–
BIN$yrMKlZaLMhfgNAgAIMenRA==$0
EMPLOYEES
You can also
view the contents of the recycle bin using the SQL*Plus commandSHOW RECYCLEBIN.
SQL> show
recyclebin
ORIGINAL
NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————-
—————————— ———— ——————-
EMPLOYEES BIN$yrMKlZaVMhfgNAgAIMenRA==$0
TABLE 2003-10-27:14:00:19
You can query
objects that are in the recycle bin, just as you can query other objects.
However, you must specify the name of the object as it is identified in the
recycle bin. For example:
SELECT * FROM
“BIN$yrMKlZaVMhfgNAgAIMenRA==$0”;Purging Objects in the Recycle Bin
If you decide
that you are never going to restore an item from therecycle bin, you can use thePURGEstatement to remove the items and
their associated objects from the recycle bin and release their storage space.
You need the same privileges as if you were dropping the item.如果您决定不再从回收站中恢复项目，则可以使用PURGE语句从回收站中删除项目及其关联对象并释放其存储空间。 您需要有与删除该项目相同的权限。
When you use
thePURGEstatement to purge a table, you
can use the name that the table is known by in the recycle bin or the original
name of the table. The recycle bin name can be obtained from either theDBA_orUSER_RECYCLEBINview as shown in”Viewing and Querying Objects in
the Recycle Bin”.当您使用PURGE语句清除表时，可以使用回收站中的表名称或表的原始名称。 可以从DBA_或USER_RECYCLEBIN视图获取回收站名称；
The following
hypothetical example purges the tablehr.int_admin_emp, which was renamed toBIN$jsleilx392mk2=293$0when it was placed in the recycle
bin:
PURGE TABLE
“BIN$jsleilx392mk2=293$0”;
You can achieve
the same result with the following statement:
PURGE TABLE
int_admin_emp;
You can use
thePURGEstatement to purge all the objects
in the recycle bin that are from a specified tablespace or only the tablespace
objects belonging to a specified user, as shown in the following examples:
PURGE TABLESPACE
example;
PURGE TABLESPACE
example USER oe;
Users can purge
the recycle bin of their own objects, and release space for objects, by using
the following statement:
PURGE
RECYCLEBIN;
If you have
theSYSDBAprivilege, then you can purge the
entire recycle bin by specifyingDBA_RECYCLEBIN, instead ofRECYCLEBINin the previous statement.如果你具有SYSDBA权限，则可以通过在上一个语句中指定DBA_RECYCLEBIN而不是RECYCLEBIN清除整个回收站。
You can also use
thePURGEstatement to purge an index from
the recycle bin or to purge from the recycle bin all objects in a specified
tablespace.你还可以使用PURGE语句从回收站中清除索引，或从回收站清除指定表空间中的所有对象。 Restoring Tables from the Recycle Bin
Use theFLASHBACKTABLE…TOBEFOREDROPstatement to recover objects from
the recycle bin. You can specify either the name of the table in therecycle bin or the original table
name. An optionalRENAME TOclause
lets you rename the table as you recover it. The recycle bin name can be
obtained from either theDBA_orUSER_RECYCLEBINview as shown in”Viewing and Querying Objects in
the Recycle Bin”. To use theFLASHBACKTABLE…TOBEFOREDROPstatement, you need the same
privileges required to drop the table.
The following
example restoresint_admin_emptable and assigns to it a new name:
FLASHBACK TABLE
int_admin_emp TO BEFORE DROP
RENAME TO int2_admin_emp;
The
system-generated recycle bin name is very useful if you have dropped a table
multiple times. For example, suppose you have three versions of theint2_admin_emptable in the recycle bin and you
want to recover the second version. You can do this by issuing twoFLASHBACK TABLEstatements, or you can query the
recycle bin and then flashback to the appropriate system-generated name, as
shown in the following example. Including the create time in the query can help
you verify that you are restoring the correct table.
SELECT
object_name, original_name, createtime FROM recyclebin;
OBJECT_NAME ORIGINAL_NAME CREATETIME
——————————
————— ——————-
BIN$yrMKlZaLMhfgNAgAIMenRA==$0
INT2_ADMIN_EMP 2006-02-05:21:05:52
BIN$yrMKlZaVMhfgNAgAIMenRA==$0
INT2_ADMIN_EMP 2006-02-05:21:25:13
BIN$yrMKlZaQMhfgNAgAIMenRA==$0
INT2_ADMIN_EMP 2006-02-05:22:05:53
FLASHBACK TABLE
“BIN$yrMKlZaVMhfgNAgAIMenRA==$0” TO BEFORE DROP;Restoring Dependent Objects
When you restore
a table from the recycle bin, dependent objects such as indexes do not get their
original names back; they retain their system-generated recycle bin names. You
must manually rename dependent objects to restore their original names. If you
plan to manually restore original names for dependent objects, ensure that you
make note of each dependent object’s system-generated recycle bin namebeforeyou
restore the table.从回收站还原表时，依赖对象（如索引）不会返回其原始名称; 它们保留系统生成的回收站名称。 您必须手动重命名依赖对象来恢复其原始名称。 如果计划手动恢复依赖对象的原始名称，请确保在还原表之前记下每个从属对象的系统生成的回收站名称。
The following is
an example of restoring the original names of some of the indexes of the
dropped tableJOB_HISTORY, from theHRsample schema. The example assumes that you are
logged in as theHRuser.
After droppingJOB_HISTORYand before restoring it from the recycle
bin, run the following query:
2. SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM RECYCLEBIN;
3.
4. OBJECT_NAME ORIGINAL_NAME TYPE
5. ——————————
————————- ——–
6. BIN$DBo9UChtZSbgQFeMiAdCcQ==$0
JHIST_JOB_IX INDEX
7. BIN$DBo9UChuZSbgQFeMiAdCcQ==$0
JHIST_EMPLOYEE_IX INDEX
8. BIN$DBo9UChvZSbgQFeMiAdCcQ==$0
JHIST_DEPARTMENT_IX INDEX
9. BIN$DBo9UChwZSbgQFeMiAdCcQ==$0
JHIST_EMP_ID_ST_DATE_PK INDEX
10. BIN$DBo9UChxZSbgQFeMiAdCcQ==$0
JOB_HISTORY TABLE
Restore the table with
the following command:
12. FLASHBACK TABLE JOB_HISTORY TO BEFORE
DROP;
Run the following query
to verify that allJOB_HISTORYindexes retained their system-generated recycle bin names:
14. SELECT INDEX_NAME FROM USER_INDEXES
WHERE TABLE_NAME = ‘JOB_HISTORY’;
15.
16. INDEX_NAME
17. ——————————
18. BIN$DBo9UChwZSbgQFeMiAdCcQ==$0
19. BIN$DBo9UChtZSbgQFeMiAdCcQ==$0
20. BIN$DBo9UChuZSbgQFeMiAdCcQ==$0
21. BIN$DBo9UChvZSbgQFeMiAdCcQ==$0
Restore the original
names of the first two indexes as follows:
23. ALTER INDEX
“BIN$DBo9UChtZSbgQFeMiAdCcQ==$0” RENAME TO JHIST_JOB_IX;
24. ALTER INDEX
“BIN$DBo9UChuZSbgQFeMiAdCcQ==$0” RENAME TO JHIST_EMPLOYEE_IX;
Note that double quotes are required around the
system-generated names.到此，关于“Oracle回收站概念及功能”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注百云网站，小编会继续努力为大家带来更多实用的文章！

相关推荐: ORA-12899: value too large for column ORG_NAME (actual: 145, maximum: 128)

导入数据时报错以下错误，这是因为原来的数据库是GBK的，每个汉字两个字节，但新数据库是AL32UT免费云主机域名F8的，每个汉字是三个字节，导致超过长度了。 解决： 先记录JOB_QUEUE_PROCESSES和AQ_TM_PROCESSES参数的值，修改后好…