|
Notice: This blog is written by Bo Tang.
- [oracle@station79 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 23 14:38:10 2019
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> @t05310.sql
- Enter value for s1: t_cascade3
- old 1: create table &&s1( id number )
- new 1: create table t_cascade3 ( id number )
- Table created.
- old 1: insert into &&s1 values (1)
- new 1: insert into t_cascade3 values (1)
- 1 row created.
- old 1: insert into &&s1 values (2)
- new 1: insert into t_cascade3 values (2)
- 1 row created.
- old 1: insert into &&s1 values (3)
- new 1: insert into t_cascade3 values (3)
- 1 row created.
- Commit complete.
- old 1: update &&s1 set id=11 where id=1
- new 1: update t_cascade3 set id=11 where id=1
- 1 row updated.
- old 1: update &&s1 set id=22 where id=2
- new 1: update t_cascade3 set id=22 where id=2
- 1 row updated.
- old 1: update &&s1 set id=33 where id=3
- new 1: update t_cascade3 set id=33 where id=3
- 1 row updated.
- Commit complete.
- old 1: update &&s1 set id=222 where id=22
- new 1: update t_cascade3 set id=222 where id=22
- 1 row updated.
- old 1: update &&s1 set id=333 where id=33
- new 1: update t_cascade3 set id=333 where id=33
- 1 row updated.
- Commit complete.
- old 1: update &&s1 set id=22 where id=222
- new 1: update t_cascade3 set id=22 where id=222
- 1 row updated.
- Commit complete.
- SQL>
复制代码- select log_mode from v$database;
- select * from flashback_transaction_query ftq
- where ftq.logon_user='HR' and ftq.table_name='T_CASCADE3' order by 2;
- begin
- dbms_flashback.transaction_backout(numtxns => 1,
- xids => xid_array('230021003F000000'));
- end;
-
复制代码
- select * from flashback_transaction_query ftq
- where ftq.logon_user='HR' and ftq.table_name='T_CASCADE3' order by 2;
- begin
- dbms_flashback.transaction_backout(numtxns => 1,
- xids => xid_array('230021003F000000'),
- options => dbms_flashback.cascade
- );
- end;
-
- select * from dba_flashback_txn_report;
复制代码- <?xml version="1.0" encoding="ISO-8859-1"?>
- <COMP_XID_REPORT XID="1D000B0041000000">
- <TRANSACTION XID="230021003F000000">
- <CHARACTERISTICS>
- </CHARACTERISTICS>
- <UNDO_SQL>
- <USQL exec="yes">
- update "HR"."T_CASCADE3" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAV3jAAEAABeINAAC'
- </USQL>
- <USQL exec="yes">
- update "HR"."T_CASCADE3" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAV3jAAEAABeINAAB'
- </USQL>
- <USQL exec="yes">
- update "HR"."T_CASCADE3" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAV3jAAEAABeINAAA'
- </USQL>
- </UNDO_SQL>
- <DEPENDENT_XIDS>
- <TRANSACTION XID="09000C00710B0000">
- <CHARACTERISTICS>
- </CHARACTERISTICS>
- <UNDO_SQL>
- <USQL exec="yes">
- update "HR"."T_CASCADE3" set "ID" = '33' where "ID" = '333' and ROWID = 'AAAV3jAAEAABeINAAC'
- </USQL>
- <USQL exec="yes">
- update "HR"."T_CASCADE3" set "ID" = '22' where "ID" = '222' and ROWID = 'AAAV3jAAEAABeINAAB'
- </USQL>
- </UNDO_SQL>
- <DEPENDENT_XIDS>
- <TRANSACTION XID="2800130041000000">
- <CHARACTERISTICS>
- </CHARACTERISTICS>
- <UNDO_SQL>
- <USQL exec="yes">
- update "HR"."T_CASCADE3" set "ID" = '222' where "ID" = '22' and ROWID = 'AAAV3jAAEAABeINAAB'
- </USQL>
- </UNDO_SQL>
- <DEPENDENT_XIDS>
- </DEPENDENT_XIDS>
- </TRANSACTION>
- </DEPENDENT_XIDS>
- </TRANSACTION>
- </DEPENDENT_XIDS>
- </TRANSACTION>
- <EXECUTED_UNDO_SQL>
- <EXEC_USQL>update "HR"."T_CASCADE3" set "ID" = '222' where "ID" = '22' and ROWID = 'AAAV3jAAEAABeINAAB'
- </EXEC_USQL>
- <EXEC_USQL>update "HR"."T_CASCADE3" set "ID" = '33' where "ID" = '333' and ROWID = 'AAAV3jAAEAABeINAAC'
- </EXEC_USQL>
- <EXEC_USQL>update "HR"."T_CASCADE3" set "ID" = '22' where "ID" = '222' and ROWID = 'AAAV3jAAEAABeINAAB'
- </EXEC_USQL>
- <EXEC_USQL>update "HR"."T_CASCADE3" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAV3jAAEAABeINAAC'
- </EXEC_USQL>
- <EXEC_USQL>update "HR"."T_CASCADE3" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAV3jAAEAABeINAAB'
- </EXEC_USQL>
- <EXEC_USQL>update "HR"."T_CASCADE3" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAV3jAAEAABeINAAA'
- </EXEC_USQL>
- </EXECUTED_UNDO_SQL>
- </COMP_XID_REPORT>
复制代码 ----------------------------------------------------------------------------------------------------
- SQL> @t05310
- Enter value for s1: t_nonconflict_only
- old 1: create table &&s1( id number )
- new 1: create table t_nonconflict_only( id number )
- Table created.
- old 1: insert into &&s1 values (1)
- new 1: insert into t_nonconflict_only values (1)
- 1 row created.
- old 1: insert into &&s1 values (2)
- new 1: insert into t_nonconflict_only values (2)
- 1 row created.
- old 1: insert into &&s1 values (3)
- new 1: insert into t_nonconflict_only values (3)
- 1 row created.
- Commit complete.
- old 1: update &&s1 set id=11 where id=1
- new 1: update t_nonconflict_only set id=11 where id=1
- 1 row updated.
- old 1: update &&s1 set id=22 where id=2
- new 1: update t_nonconflict_only set id=22 where id=2
- 1 row updated.
- old 1: update &&s1 set id=33 where id=3
- new 1: update t_nonconflict_only set id=33 where id=3
- 1 row updated.
- Commit complete.
- old 1: update &&s1 set id=222 where id=22
- new 1: update t_nonconflict_only set id=222 where id=22
- 1 row updated.
- old 1: update &&s1 set id=333 where id=33
- new 1: update t_nonconflict_only set id=333 where id=33
- 1 row updated.
- Commit complete.
- old 1: update &&s1 set id=22 where id=222
- new 1: update t_nonconflict_only set id=22 where id=222
- 1 row updated.
- Commit complete.
- SQL>
复制代码- select * from flashback_transaction_query ftq
- where ftq.logon_user='HR' and ftq.table_name='T_NONCONFLICT_ONLY' order by 2;
- --0C000800AE020000
- begin
- dbms_flashback.transaction_backout(numtxns => 1,
- xids => xid_array('0C000800AE020000'),
- options => dbms_flashback.nonconflict_only
- );
- end;
复制代码- <?xml version="1.0" encoding="ISO-8859-1"?>
- <COMP_XID_REPORT XID="11000100B6020000" NAME="_SYS_COMP_TXN_3672269_TIM_1571815103">
- <TRANSACTION XID="0C000800AE020000">
- <CHARACTERISTICS>
- </CHARACTERISTICS>
- <UNDO_SQL>
- <USQL exec="no">
- update "HR"."T_NONCONFLICT_ONLY" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAV3pAAEAABeIVAAC'
- </USQL>
- <USQL exec="no">
- update "HR"."T_NONCONFLICT_ONLY" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAV3pAAEAABeIVAAB'
- </USQL>
- <USQL exec="yes">
- update "HR"."T_NONCONFLICT_ONLY" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAV3pAAEAABeIVAAA'
- </USQL>
- </UNDO_SQL>
- <DEPENDENT_XIDS>
- <TRANSACTION XID="080018009C0B0000">
- <CHARACTERISTICS>
- </CHARACTERISTICS>
- <UNDO_SQL>
- <USQL exec="no">
- update "HR"."T_NONCONFLICT_ONLY" set "ID" = '33' where "ID" = '333' and ROWID = 'AAAV3pAAEAABeIVAAC'
- </USQL>
- <USQL exec="no">
- update "HR"."T_NONCONFLICT_ONLY" set "ID" = '22' where "ID" = '222' and ROWID = 'AAAV3pAAEAABeIVAAB'
- </USQL>
- </UNDO_SQL>
- <DEPENDENT_XIDS>
- <TRANSACTION XID="11001F00B5020000">
- <CHARACTERISTICS>
- </CHARACTERISTICS>
- <UNDO_SQL>
- <USQL exec="no">
- update "HR"."T_NONCONFLICT_ONLY" set "ID" = '222' where "ID" = '22' and ROWID = 'AAAV3pAAEAABeIVAAB'
- </USQL>
- </UNDO_SQL>
- <DEPENDENT_XIDS>
- </DEPENDENT_XIDS>
- </TRANSACTION>
- </DEPENDENT_XIDS>
- </TRANSACTION>
- </DEPENDENT_XIDS>
- </TRANSACTION>
- <EXECUTED_UNDO_SQL>
- <EXEC_USQL>update "HR"."T_NONCONFLICT_ONLY" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAV3pAAEAABeIVAAA'
- </EXEC_USQL>
- </EXECUTED_UNDO_SQL>
- </COMP_XID_REPORT>
复制代码
------------------------------------------------------
- SQL> @t05310
- Enter value for s1: t_nocascade_only
- old 1: create table &&s1( id number )
- new 1: create table t_nocascade_only( id number )
- Table created.
- old 1: insert into &&s1 values (1)
- new 1: insert into t_nocascade_only values (1)
- 1 row created.
- old 1: insert into &&s1 values (2)
- new 1: insert into t_nocascade_only values (2)
- 1 row created.
- old 1: insert into &&s1 values (3)
- new 1: insert into t_nocascade_only values (3)
- 1 row created.
- Commit complete.
- old 1: update &&s1 set id=11 where id=1
- new 1: update t_nocascade_only set id=11 where id=1
- 1 row updated.
- old 1: update &&s1 set id=22 where id=2
- new 1: update t_nocascade_only set id=22 where id=2
- 1 row updated.
- old 1: update &&s1 set id=33 where id=3
- new 1: update t_nocascade_only set id=33 where id=3
- 1 row updated.
- Commit complete.
- old 1: update &&s1 set id=222 where id=22
- new 1: update t_nocascade_only set id=222 where id=22
- 1 row updated.
- old 1: update &&s1 set id=333 where id=33
- new 1: update t_nocascade_only set id=333 where id=33
- 1 row updated.
- Commit complete.
- old 1: update &&s1 set id=22 where id=222
- new 1: update t_nocascade_only set id=22 where id=222
- 1 row updated.
- Commit complete.
- SQL>
复制代码- select * from flashback_transaction_query ftq
- where ftq.logon_user='HR' and ftq.table_name='T_NOCASCADE_FORCE' order by 2;
- --1D00080042000000
- begin
- dbms_flashback.transaction_backout(numtxns => 1,
- xids => xid_array('1D00080042000000'),
- options => dbms_flashback.nocascade_force
- );
- end;
复制代码- <?xml version="1.0" encoding="ISO-8859-1"?>
- <COMP_XID_REPORT XID="0B001200AF020000" NAME="_SYS_COMP_TXN_3672269_TIM_1571815606">
- <TRANSACTION XID="1D00080042000000">
- <CHARACTERISTICS>
- </CHARACTERISTICS>
- <UNDO_SQL>
- <USQL exec="yes">
- update "HR"."T_NOCASCADE_FORCE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAV3rAAEAABeIlAAC'
- </USQL>
- <USQL exec="yes">
- update "HR"."T_NOCASCADE_FORCE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAV3rAAEAABeIlAAB'
- </USQL>
- <USQL exec="yes">
- update "HR"."T_NOCASCADE_FORCE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAV3rAAEAABeIlAAA'
- </USQL>
- </UNDO_SQL>
- <DEPENDENT_XIDS>
- <TRANSACTION XID="18000900B6020000">
- <CHARACTERISTICS>
- </CHARACTERISTICS>
- <UNDO_SQL>
- <USQL exec="no">
- update "HR"."T_NOCASCADE_FORCE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAAV3rAAEAABeIlAAC'
- </USQL>
- <USQL exec="no">
- update "HR"."T_NOCASCADE_FORCE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAAV3rAAEAABeIlAAB'
- </USQL>
- </UNDO_SQL>
- <DEPENDENT_XIDS>
- <TRANSACTION XID="2600120041000000">
- <CHARACTERISTICS>
- </CHARACTERISTICS>
- <UNDO_SQL>
- <USQL exec="no">
- update "HR"."T_NOCASCADE_FORCE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAAV3rAAEAABeIlAAB'
- </USQL>
- </UNDO_SQL>
- <DEPENDENT_XIDS>
- </DEPENDENT_XIDS>
- </TRANSACTION>
- </DEPENDENT_XIDS>
- </TRANSACTION>
- </DEPENDENT_XIDS>
- </TRANSACTION>
- <EXECUTED_UNDO_SQL>
- <EXEC_USQL>update "HR"."T_NOCASCADE_FORCE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAV3rAAEAABeIlAAC'
- </EXEC_USQL>
- <EXEC_USQL>update "HR"."T_NOCASCADE_FORCE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAV3rAAEAABeIlAAB'
- </EXEC_USQL>
- <EXEC_USQL>update "HR"."T_NOCASCADE_FORCE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAV3rAAEAABeIlAAA'
- </EXEC_USQL>
- </EXECUTED_UNDO_SQL>
- </COMP_XID_REPORT>
复制代码 ----------------------------------
一下是我们实验的结果:
原表是:
11
22
333
- SQL> select * from t_nocascade_force;
- ID
- ----------
- 1
- 2
- 333
- SQL> select * from t_nonconflict_only;
- ID
- ----------
- 1
- 22
- 333
- SQL> select * from t_cascade3;
- ID
- ----------
- 1
- 2
- 3
- SQL>
复制代码
Botang唐波 8:03:04
如果没有第4个事务:
Botang唐波 8:03:42
原表: 11 222 333
t_cascade3 : 1 2 3
Botang唐波 8:04:25
t_nonconflict_only: 1 222 333(只做第一行)
Botang唐波 8:04:49
t_nocascade_force: 1 222 333(3行都做,但是where a=11能碰上, where a=22碰不上, where a=33也碰不上; 11/22/33是要挖掉事务的“丰功伟绩”)
--------------------------------------------------------------------
闪回7(支持闪回2/3):
闪回1是闪回8的导航器;闪回3是闪回4的导航器,闪回7支持闪回2/3。
- create flashback archive fda1 tablespace users quota 10M retention 10 year;
- grant flashback archive on fda1 to hr;
复制代码- [oracle@station79 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 23 16:18:38 2019
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> alter table employees flashback archive fda1;
- Table altered.
- SQL>
复制代码
- select * from dba_flashback_archive;
- select * from dba_flashback_archive_tables;
- select * from dba_flashback_archive_ts;
复制代码
| OWNER_NAME | FLASHBACK_ARCHIVE_NAME | FLASHBACK_ARCHIVE# | RETENTION_IN_DAYS | CREATE_TIME | LAST_PURGE_TIME | STATUS | 1 | SYS | FDA1 | 1 | 3650 | 23-10月-19 04.16.50.000000000 下午 | 23-10月-19 04.16.50.000000000 下午 | |
| TABLE_NAME | OWNER_NAME | FLASHBACK_ARCHIVE_NAME | ARCHIVE_TABLE_NAME | STATUS | 1 | EMPLOYEES | HR | FDA1 | SYS_FBA_HIST_88047 | ENABLED |
| FLASHBACK_ARCHIVE_NAME | FLASHBACK_ARCHIVE# | TABLESPACE_NAME | QUOTA_IN_MB | 1 | FDA1 | 1 | USERS | 10 |
- SQL> update employees set salary=60000 where employee_id=100;
- 1 row updated.
- SQL> commit;
- Commit complete.
- SQL> select * from hr.SYS_FBA_HIST_88047;
- select * from hr.SYS_FBA_HIST_88047
- *
- ERROR at line 1:
- ORA-00942: table or view does not exist
复制代码
等待:
- SQL> /
- RID
- --------------------------------------------------------------------------------
- STARTSCN ENDSCN XID O EMPLOYEE_ID FIRST_NAME
- ---------- ---------- ---------------- - ----------- --------------------
- LAST_NAME EMAIL PHONE_NUMBER
- ------------------------- ------------------------- --------------------
- HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID
- ------------------- ---------- ---------- -------------- ----------
- DEPARTMENT_ID
- -------------
- AAAVTFAAFAAAADNAAC
- 4962452 100 Steven
- King YYYY 515.123.4567
- RID
- --------------------------------------------------------------------------------
- STARTSCN ENDSCN XID O EMPLOYEE_ID FIRST_NAME
- ---------- ---------- ---------------- - ----------- --------------------
- LAST_NAME EMAIL PHONE_NUMBER
- ------------------------- ------------------------- --------------------
- HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID
- ------------------- ---------- ---------- -------------- ----------
- DEPARTMENT_ID
- -------------
- 2003-06-17:00:00:00 AD_PRES 50000
- 90
复制代码- SQL> create table t_part1 ( a number )
- 2 partition by range(a)
- 3 (partition t_part1_p1 values less than (100),
- 4 partition t_part1_p2 values less than (maxvalue));
- Table created.
- SQL> insert into t_part1 values (100);
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> insert into t_part1 values(1);
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> select * from t_part1;
- A
- ----------
- 1
- 100
- SQL> select * from t_part1 (t_part1_p1);
- select * from t_part1 (t_part1_p1)
- *
- ERROR at line 1:
- ORA-00933: SQL command not properly ended
- SQL> select * from t_part1 partition (t_part1_p1);
- A
- ----------
- 1
- SQL> select * from t_part1 partition (t_part1_p2);
- A
- ----------
- 100
- SQL>
复制代码- alter flashback archive fda1 set default;
复制代码- SQL> alter table t_part1 flashback archive fda1;
- Table altered.
- SQL> alter table t_part1 flashback archive;
- alter table t_part1 flashback archive
- *
- ERROR at line 1:
- ORA-55600: The table "HR"."T_PART1" is already enabled for Flashback Archive
复制代码
OTR框架下,有一些DDL操作不能执行:
- SQL> drop table t_part1;
- drop table t_part1
- *
- ERROR at line 1:
- ORA-55610: Invalid DDL statement on history-tracked table
- SQL>
复制代码- select * from dba_tab_partitions tp where tp.table_owner='HR' and tp.table_name='T_PART1';
复制代码- SQL> alter table t_part1 split partition t_part1_p1 at (50) into ( partition t_part1_p11, partition t_part1_p12) ;
- alter table t_part1 split partition t_part1_p1 at (50) into ( partition t_part1_p11, partition t_part1_p12)
- *
- ERROR at line 1:
- ORA-55610: Invalid DDL statement on history-tracked table
复制代码
- select * from dba_flashback_archive_tables;
- select * from hr.SYS_FBA_HIST_89592;
- update hr.SYS_FBA_HIST_89592 set a=10 where a=1;
复制代码
- begin
- dbms_flashback_archive.disassociate_fba(owner_name => 'HR',
- table_name => 'T_PART1');
- end;
复制代码- SQL> alter table t_part1 split partition t_part1_p1 at (50) into ( partition t_part1_p11, partition t_part1_p12) ;
- Table altered.
复制代码- update hr.SYS_FBA_HIST_89592 set a=10 where a=1;
- commit;
复制代码- begin
- dbms_flashback_archive.reassociate_fba(owner_name => 'HR',
- table_name => 'T_PART1');
- end;
复制代码- select versions_xid, versions_operation, versions_startscn , a from hr.t_part1
- versions between scn minvalue and maxvalue;
复制代码
|
|