17.08.18
flashback
功能 依赖组件 相关参数 典型错误
query undo tbs undo_retention dml
version query undo tbs undo_retention dml
flashback table undo tbs undo_retention dml
flashback drop recyclebin recyclebin, freespace drop table
transaction query supplemental log dml
fda flashback archive dml
database flashback log db_flashback_retention_target ddl
sys不允许闪回，创建新用户
SQL> create user user01 identified by password;
SQL> grant dba to user01;
SQL> conn user01/password
flashback query
user01:
SQL> create table t1(x int);
SQL> create index t1_x_idx on t1(x);
SQL> insert into t1 values (1);
SQL> commit;
SQL> select sysdate from dual;
SQL> select dbms_flashback.get_system_change_number from dual;
SQL> delete t1;
SQL> commit;
SQL> select * from t1;
SQL> select * from t1 as of scn 1446069;
SQL> select * from t1 as of timestamp to_timestamp('2015-10-28 10:31:54', 'yyyy-mm-dd hh24:mi:ss');
SQL> truncate table t1;或alter table t1 move;或收缩数据文件
SQL> select * from t1 as of scn 1446069; 物理结构变化，闪回失败
logminer
flashback version query
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> update t1 set x=2;
SQL> commit;
SQL> update t1 set x=3;
SQL> commit;
SQL> update t1 set x=4;
SQL> commit;
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation, x
from t1
versions between scn minvalue and maxvalue
order by versions_starttime;
versions between timestamp to_timestamp('2015-10-28 9:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_timestamp('2015-10-28 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
SQL> truncate table t1; 物理结构改变，查询失败
flashback table
SQL> conn user01/password
SQL> create table my_dept(deptno int primary key, dname varchar2(20));
SQL> create table my_emp(empno int primary key, deptno int references my_dept);
SQL> insert into my_dept values (10, 'sales');
SQL> insert into my_emp values (100, 10);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;
SQL> delete my_emp;
SQL> delete my_dept;
SQL> commit;
SQL> alter table my_dept enable row movement;
SQL> alter table my_emp enable row movement;
SQL> flashback table my_emp to scn 1451706; 失败
SQL> flashback table my_dept to scn 1451706;
SQL> flashback table my_emp to scn 1451706;
SQL> select INDEX_NAME, STATUS from user_indexes where table_name='MY_EMP';自动维护索引
SQL> select INDEX_NAME, STATUS from user_indexes where table_name='MY_DEPT';
SQL> truncate table my_emp;
SQL> flashback table my_emp to scn 1451706; 失败
flashback drop
SQL> show parameter recyclebin
SQL> purge recyclebin; 清空回收站
SQL> create tablespace tbs01 datafile '/home/oracle/tbs01.dbf' size 5M;
SQL> create table t1 tablespace tbs01 as select * from dba_objects where rownum<=20000;
SQL> create index t1_object_id_idx on t1(object_id) tablespace tbs01;
SQL> select INDEX_NAME from user_indexes where TABLE_NAME='T1';
SQL> drop table t1;
SQL> select table_name from user_tables;
SQL> show recyclebin
SQL> select object_name, original_name, type, droptime from user_recyclebin; 包含index
SQL> select count(*) from "BIN$IyKOcy5jPo7gUwEAqMCBEg==$0";
SQL> flashback table t1 to before drop;
SQL> select INDEX_NAME from user_indexes where TABLE_NAME='T1';
SQL> alter index "BIN$LRyc7hA1JaPgUwEAqMDzWw==$0" rename to T1_OBJECT_ID_IDX; 恢复index名称
重名的处理：
SQL> flashback table "BIN$IyKOcy5jPo7gUwEAqMCBEg==$0" to before drop;
SQL> flashback table t1 to before drop rename to t2;
SQL> drop table t1;
SQL> show recyclebin 在回收站中
SQL> create table t2 tablespace tbs01 as select * from dba_objects where rownum<=30000;
SQL> show recyclebin t1被覆盖
SQL> drop table t2 purge;
SQL> purge recyclebin
flashback transaction query
SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data (primary key) columns;
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> update t1 set x=11 where x=1; 误操作的事务
SQL> commit;
SQL> insert into t1 values (2);
SQL> commit;
select versions_starttime, versions_endtime, versions_xid, versions_operation, x
from t1
versions between scn minvalue and maxvalue
order by versions_starttime; 获取误操作事务的xid
SQL> select UNDO_SQL, OPERATION from flashback_transaction_query where xid='02000F0059040000';
flashback database
SQL> shutdown immediate
SQL> startup mount
SQL> alter database flashback on; 数据库在归档模式下
SQL> show parameter db_flashback_retention_target
SQL> select OLDEST_FLASHBACK_TIME from v$flashback_database_log;
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual; 获取scn
SQL> truncate table t1;
SQL> create table after_truncate(x int); 其他正确操作
SQL> select OLDEST_FLASHBACK_TIME, OLDEST_FLASHBACK_SCN from v$flashback_database_log; 确认是否在恢复范围
SQL> shutdown abort
SQL> startup mount
SQL> flashback database to scn 1495195;
SQL> alter database open resetlogs;
SQL> select * from t1;
SQL> select * from after_truncate; 消失
移动数据
sqlloader
SQL> create table t1(id int constraint t1_id_pk primary key, name varchar2(20), salary int constraint t1_salary_ck check(salary>0));
$ vi ~/loader.dat
100,"abc",1000
100,"def",2000
102,"xyz",-1000
em中常规导入，自动处理违反约束的记录
em中直接导入
SQL> select CONSTRAINT_NAME, STATUS from user_constraints where TABLE_NAME='T1';
SQL> select INDEX_NAME, STATUS from user_indexes where TABLE_NAME='T1';
SQL> alter table t1 enable validate constraint T1_SALARY_CK; 失败
SQL> @?/rdbms/admin/utlexpt1.sql
处理check约束：
SQL> alter table t1 enable validate constraint T1_SALARY_CK exceptions into exceptions;
SQL> select * from t1 where rowid in(select ROW_ID from exceptions);
SQL> update t1 set salary=abs(salary) where id=102;
SQL> truncate table exceptions;
SQL> alter table t1 enable validate constraint T1_SALARY_CK exceptions into exceptions;
处理pk约束：
SQL> alter table t1 disable novalidate constraint T1_ID_PK;
SQL> alter table t1 enable validate constraint T1_ID_PK exceptions into exceptions;
SQL> select * from t1 where rowid in(select ROW_ID from exceptions);
SQL> update t1 set id=101 where name='def';
SQL> truncate table exceptions;
SQL> alter table t1 enable validate constraint T1_ID_PK exceptions into exceptions;
SQL> select INDEX_NAME, STATUS from user_indexes where TABLE_NAME='T1';
外部表
oracle_datapump driver
unloading:
CREATE TABLE oe.inventories_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY DATA_PUMP_DIR
LOCATION ('inv_xt.dmp')
)
AS SELECT * FROM oe.inventories;
SQL> delete oe.inventories_xt; 失败
loading:
CREATE TABLE oe.inventories_xt2
(
product_id NUMBER(6),
warehouse_id NUMBER(3),
quantity_on_hand NUMBER(8)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY DATA_PUMP_DIR
LOCATION ('inv_xt.dmp')
);
SQL> delete oe.inventories_xt2; 失败
