oracle归档开关,ORACLE 11G flashback table删除功能开关

从10g开始,表及其依赖对象不会被立即从数据库里删除，它们被重命名，暂时存放在原位置；oracle根据空间的使用情况来来决定保留时间。这些删除对象在recylebin数据字典表里，是否开启这个功能来支持flashback table操作与recyclebin参数有关，11g中默认是on即打开的

SQL> conn trsen/trsen

Connected.

SQL> show parameter recyclebin;

NAME TYPE VALUE

———————————— ———– ——————————

recyclebin string on

SQL> desc t;

Name Null? Type

—————————————– ——– —————————-

OBJECT_NAME VARCHAR2(128)

SQL> drop table t;

Table dropped.

SQL> desc t;

ERROR:

ORA-04043: object t does not exist

SQL> flashback table t to before drop;

Flashback complete.

SQL> desc t;

Name Null? Type

—————————————– ——– —————————-

OBJECT_NAME VARCHAR2(128)

SQL> alter system set recyclebin=off scope=spfile;

System altered.

SQL> conn sys/oracle as sysdba

Connected.

SQL> startup force;

ORACLE instance started.

Total System Global Area 476004352 bytes

Fixed Size 2214496 bytes

Variable Size 310379936 bytes

Database Buffers 159383552 bytes

Redo Buffers 4026368 bytes

Database mounted.

Database opened.

SQL> show parameter recyclebin;

NAME TYPE VALUE

———————————— ———– ——————————

recyclebin string OFF

SQL> conn trsen/trsen;

Connected.

SQL> desc t;

Name Null? Type

—————————————– ——– —————————-

OBJECT_NAME VARCHAR2(128)

SQL> drop table t;

Table dropped.

SQL> desc t

ERROR:

ORA-04043: object t does not exist

SQL> flashback table t to before drop;

flashback table t to before drop

*

ERROR at line 1:

ORA-38305: object not in RECYCLE BIN

1.可以使用purge table xxx;手工清理recyclebin里的信息

2.数据库空间不够，会自动去清理recyclebin的信息