首页 > 代码库 > 闪回之闪回表（flashback table）
闪回之闪回表（flashback table）
背景知识
设置闪回数据库参数
设置数据库闪回的三个参数：
db_recovery_file_dest_size 闪回恢复区大小
db_recovery_file_dest 闪回恢复区路径，该参数可以任意指定，闪回日志记录了数据库的前影像，该日志不会进行归档，一但停用数据库的闪回功能，该目录下的日志会
自动清除
db_flashback_retention_target 保留恢复最近多长时间的数据，单位为分钟。
SQL> alter system set db_recovery_file_dest_size=5G;
System altered.
SQL> alter system set db_recovery_file_dest=‘/home/oracle/flash‘;
System altered.
SQL> alter system set db_flashback_retention_target=2440; --分钟 2天，系统默认是1天
System altered.
回收站（recyclebin)：从管理的角度为每个用户“分配”一个回收站，但这个回收站并不实际开辟空间(逻辑容器），当drop table时（非purge），原来的表所使用的段中的数据并没有真正的删除。实际上是把table的段名以回收站方式重命名。该段所在表空间不足需要扩展时，回收站中的信息会被自动清除。
一：
闪回一张无索引与约束的普通表
-- 查看是否有闪回功能 ON表示闪回已开 OFF表示闪回关闭
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ---------
recyclebin string on
SQL> desc andy.andy;
Name Null? Type
----------------------------------------- -------- --------------
ID NUMBER(38)
SQL> drop table andy.andy;
Table dropped.
SQL> select * from andy.andy;
select * from andy.andy
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> flashback table andy.andy to before drop;
Flashback complete.
SQL> select * from andy.andy;
ID
----------
100
二：
闪回一张带索引与约束的表
1） 准备环境
SQL>
create table school(sid number(4),sname varchar2(400 char), check_status number(1) default 0 check( check_status in(0,1)),accountant varchar2(20 char)unique,pwd varchar2(20 char),email varchar2(30 char),photo_path varchar2(800 char),
constraint pk_t_school primary key(sid));
Table created.
SQL> insert into andy.school(sid,sname) values(1,‘andy‘);
1 row created.
SQL> insert into andy.school(sid,sname) values(2,‘andy‘);
1 row created.
SQL> col sname for a40
SQL> select sid,sname from andy.school;
SID SNAME
---------- ----------------------------------------
1 andy
2 andy
-- 用andy用户登录
sqlplus andy/andy@10.100.25.13:1521/orcl
2）查一下约束和索引
SQL> SELECT index_name, column_name, descend FROM user_ind_columns WHERE table_name = ‘SCHOOL‘;
INDEX_NAME COLUMN_NAME DESC
------------------------------ ---------------------------------------- ----
BIN$CgrA2XbJP/jgUGQKDRkLsQ==$0 SID ASC
BIN$CgrA2XbKP/jgUGQKDRkLsQ==$0 ACCOUNTANT ASC
SQL> select CONSTRAINT_NAME,TABLE_NAME, COLUMN_NAME from user_cons_columns where TABLE_NAME=‘SCHOOL‘;
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------
BIN$CgrA2XbGP/jgUGQKDRkLsQ==$0 SCHOOL CHECK_STATUS
BIN$CgrA2XbHP/jgUGQKDRkLsQ==$0 SCHOOL SID
BIN$CgrA2XbIP/jgUGQKDRkLsQ==$0 SCHOOL ACCOUNTANT
3）表被drop到回收站，查一查约束和索引
SQL> drop table school;
Table dropped.
SQL> select INDEX_NAME,TABLE_NAME from user_indexes where table_name =‘SCHOOL‘;
no rows selected
SQL> select CONSTRAINT_NAME,TABLE_NAME from user_constraints where table_name=‘SCHOOL‘;
no rows selected
4）闪回表，查一查约束与索引
SQL> flashback table school to before drop;
Flashback complete.
SQL> SELECT index_name, column_name, descend FROM user_ind_columns WHERE table_name = ‘SCHOOL‘;
INDEX_NAME COLUMN_NAME DESC
------------------------------ ---------------------------------------- ----
BIN$CgrA2XbJP/jgUGQKDRkLsQ==$0 SID ASC
BIN$CgrA2XbKP/jgUGQKDRkLsQ==$0 ACCOUNTANT ASC
SQL> select CONSTRAINT_NAME,TABLE_NAME, COLUMN_NAME from user_cons_columns where TABLE_NAME=‘SCHOOL‘;
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------
BIN$CgrA2XbGP/jgUGQKDRkLsQ==$0 SCHOOL CHECK_STATUS
BIN$CgrA2XbHP/jgUGQKDRkLsQ==$0 SCHOOL SID
BIN$CgrA2XbIP/jgUGQKDRkLsQ==$0 SCHOOL ACCOUNTANT
5）分别重命名索引和约束
SQL> alter index "BIN$CgrA2XbJP/jgUGQKDRkLsQ==$0" rename to PK_T_SCHOOL;
Index altered.
SQL> alter index "BIN$CgrA2XbKP/jgUGQKDRkLsQ==$0" rename to SYS_C0024861;
Index altered.
SQL> alter table school rename constraint "BIN$CgrA2XbGP/jgUGQKDRkLsQ==$0" to SYS_C0024859;
Table altered.
SQL> alter table school rename constraint "BIN$CgrA2XbHP/jgUGQKDRkLsQ==$0" to PK_T_SCHOOL ;
Table altered.
SQL> alter table school rename constraint "BIN$CgrA2XbIP/jgUGQKDRkLsQ==$0" to SYS_C0024861;
Table altered.
6）查看修改后的索引名与约束名
SQL> SELECT index_name, column_name, descend FROM user_ind_columns WHERE table_name = ‘SCHOOL‘;
INDEX_NAME COLUMN_NAME DESC
------------------------------ ---------------------------------------- ----
PK_T_SCHOOL SID ASC
SYS_C0024861 ACCOUNTANT ASC
SQL> select CONSTRAINT_NAME,TABLE_NAME, COLUMN_NAME from user_cons_columns where TABLE_NAME=‘SCHOOL‘;
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------
SYS_C0024859 SCHOOL CHECK_STATUS
PK_T_SCHOOL SCHOOL SID
SYS_C0024861 SCHOOL ACCOUNTANT
OK，转载请标明出处。
闪回之闪回表（flashback table）