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;             失败

posted on 2017-08-19 09:19  zhang_x  阅读(159)  评论(0编辑  收藏  举报

导航