Bo's Oracle Station › 博客首页 ›Oracle技术› 开发和基础管理 › 查看内容

【博客文章2021】在Flashback Transaction中处理事务的依赖关系和主键外键约束

2021-11-3 15:40| 发布者: admin| 查看: 1| 评论: 0|原作者: Bo Tang

摘要: 【博客文章2021】在Flashback Transaction操作中处理事务的依赖关系和主键外键约束Author: Bo Tang1.补充日志：select d.LOG_MODE, d.SUPPLEMENTAL_LOG_DATA_MIN, d.SUPPLEMENTAL_LOG_DATA_PK, d.SUPPLEMENTAL_LOG_DATA_FKfrom v_$database d;复制代码 LOG_MODESUPPLEMENTAL_LOG_DATA_MINSUPPLEMENTAL_LOG_

【博客文章2021】在Flashback Transaction操作中处理事务的依赖关系和主键外键约束

Author: Bo Tang

1. 补充日志：

select d.LOG_MODE,
d.SUPPLEMENTAL_LOG_DATA_MIN,
d.SUPPLEMENTAL_LOG_DATA_PK,
d.SUPPLEMENTAL_LOG_DATA_FK
from v_$database d;

复制代码

	LOG_MODE	SUPPLEMENTAL_LOG_DATA_MIN	SUPPLEMENTAL_LOG_DATA_PK	SUPPLEMENTAL_LOG_DATA_FK
1	ARCHIVELOG	NO	NO	NO

alter database add supplemental log data;
alter database add supplemental log data ( primary key ) columns ;

复制代码

t05310.sql (388 Bytes, 下载次数: 340)

select ftq.xid,ftq.commit_scn ,ftq.operation ,ftq.undo_sql
from flashback_transaction_query ftq
where ftq.table_owner='HR'
and ftq.table_name='T_NOCASCADE'
order by ftq.commit_scn ;

复制代码

	XID	COMMIT_SCN	OPERATION	UNDO_SQL
1	0900210089060000	2377212	INSERT	delete from "HR"."T_NOCASCADE" where ROWID = 'AAAT4GAAEAAAAIfAAB';
2	0900210089060000	2377212	INSERT	delete from "HR"."T_NOCASCADE" where ROWID = 'AAAT4GAAEAAAAIfAAA';
3	0900210089060000	2377212	INSERT	delete from "HR"."T_NOCASCADE" where ROWID = 'AAAT4GAAEAAAAIfAAC';
4	0A001000C3050000	2377217	UPDATE	update "HR"."T_NOCASCADE" set "ID" = '3' where ROWID = 'AAAT4GAAEAAAAIfAAC';
5	0A001000C3050000	2377217	UPDATE	update "HR"."T_NOCASCADE" set "ID" = '1' where ROWID = 'AAAT4GAAEAAAAIfAAA';
6	0A001000C3050000	2377217	UPDATE	update "HR"."T_NOCASCADE" set "ID" = '2' where ROWID = 'AAAT4GAAEAAAAIfAAB';
7	010020000C060000	2377221	UPDATE	update "HR"."T_NOCASCADE" set "ID" = '22' where ROWID = 'AAAT4GAAEAAAAIfAAB';
8	010020000C060000	2377221	UPDATE	update "HR"."T_NOCASCADE" set "ID" = '33' where ROWID = 'AAAT4GAAEAAAAIfAAC';
9	0500140041070000	2377224	UPDATE	update "HR"."T_NOCASCADE" set "ID" = '222' where ROWID = 'AAAT4GAAEAAAAIfAAB';

begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('0A001000C3050000'),
options => dbms_flashback.nocascade);
end;

复制代码

begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('0A001000C3050000'),
options => dbms_flashback.cascade);
end;
select * from dba_flashback_txn_report;

复制代码

	COMPENSATING_XID	COMPENSATING_TXN_NAME	COMMIT_TIME	XID_REPORT	USERNAME
1	06002100C8060000			<CLOB>	SYS

CLOB:
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="06002100C8060000">
<TRANSACTION XID="0A001000C3050000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
      <USQL exec="yes">
      update "HR"."T_NOCASCADE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAT4GAAEAAAAIfAAC'
      </USQL>
      <USQL exec="yes">
      update "HR"."T_NOCASCADE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAT4GAAEAAAAIfAAB'
      </USQL>
      <USQL exec="yes">
      update "HR"."T_NOCASCADE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAT4GAAEAAAAIfAAA'
      </USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
      <TRANSACTION XID="010020000C060000">
      <CHARACTERISTICS>
      </CHARACTERISTICS>
      <UNDO_SQL>
         <USQL exec="yes">
         update "HR"."T_NOCASCADE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAAT4GAAEAAAAIfAAC'
         </USQL>
         <USQL exec="yes">
         update "HR"."T_NOCASCADE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAAT4GAAEAAAAIfAAB'
         </USQL>
      </UNDO_SQL>
      <DEPENDENT_XIDS>
         <TRANSACTION XID="0500140041070000">
         <CHARACTERISTICS>
         </CHARACTERISTICS>
         <UNDO_SQL>
            <USQL exec="yes">
               update "HR"."T_NOCASCADE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAAT4GAAEAAAAIfAAB'
            </USQL>
         </UNDO_SQL>
         <DEPENDENT_XIDS>
         </DEPENDENT_XIDS>
         </TRANSACTION>
      </DEPENDENT_XIDS>
      </TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>update "HR"."T_NOCASCADE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAAT4GAAEAAAAIfAAB'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_NOCASCADE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAAT4GAAEAAAAIfAAC'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_NOCASCADE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAAT4GAAEAAAAIfAAB'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_NOCASCADE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAT4GAAEAAAAIfAAC'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_NOCASCADE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAT4GAAEAAAAIfAAB'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_NOCASCADE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAT4GAAEAAAAIfAAA'
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>
---------------------------

commit;
SQL> select * from t_nocascade;

ID
----------
   1
   2
   3
-----------------------------------------------------------------------------------------------------------------------------------------------------

select ftq.xid,ftq.commit_scn ,ftq.operation ,ftq.undo_sql
from flashback_transaction_query ftq
where ftq.table_owner='HR'
and ftq.table_name='T_NOCASCADE_FORCE'
order by ftq.commit_scn ;

复制代码

	XID	COMMIT_SCN	OPERATION	UNDO_SQL
1	07001F0004060000	2381390	INSERT	delete from "HR"."T_NOCASCADE_FORCE" where ROWID = 'AAAT4MAAEAAAAInAAA';
2	07001F0004060000	2381390	INSERT	delete from "HR"."T_NOCASCADE_FORCE" where ROWID = 'AAAT4MAAEAAAAInAAB';
3	07001F0004060000	2381390	INSERT	delete from "HR"."T_NOCASCADE_FORCE" where ROWID = 'AAAT4MAAEAAAAInAAC';
4	0A001C00A7050000	2381395	UPDATE	update "HR"."T_NOCASCADE_FORCE" set "ID" = '2' where ROWID = 'AAAT4MAAEAAAAInAAB';
5	0A001C00A7050000	2381395	UPDATE	update "HR"."T_NOCASCADE_FORCE" set "ID" = '1' where ROWID = 'AAAT4MAAEAAAAInAAA';
6	0A001C00A7050000	2381395	UPDATE	update "HR"."T_NOCASCADE_FORCE" set "ID" = '3' where ROWID = 'AAAT4MAAEAAAAInAAC';
7	0400160012060000	2381399	UPDATE	update "HR"."T_NOCASCADE_FORCE" set "ID" = '22' where ROWID = 'AAAT4MAAEAAAAInAAB';
8	0400160012060000	2381399	UPDATE	update "HR"."T_NOCASCADE_FORCE" set "ID" = '33' where ROWID = 'AAAT4MAAEAAAAInAAC';
9	09000400B7060000	2381402	UPDATE	update "HR"."T_NOCASCADE_FORCE" set "ID" = '222' where ROWID = 'AAAT4MAAEAAAAInAAB';

begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('0A001C00A7050000'),
options => dbms_flashback.nocascade_force);
end;
select * from dba_flashback_txn_report;

复制代码

	COMPENSATING_XID	COMPENSATING_TXN_NAME	COMMIT_TIME	XID_REPORT	USERNAME
1	06002100C8060000		8/10/2018 8:08:13 PM	<CLOB>	SYS
2	02000700B0060000	_SYS_COMP_TXN_13173587_TIM_1533903232		<CLOB>	SYS

CLOB：
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="02000700B0060000" NAME="_SYS_COMP_TXN_13173587_TIM_1533903232">
<TRANSACTION XID="0A001C00A7050000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
      <USQL exec="yes">
      update "HR"."T_NOCASCADE_FORCE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAT4MAAEAAAAInAAC'
      </USQL>
      <USQL exec="yes">
      update "HR"."T_NOCASCADE_FORCE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAT4MAAEAAAAInAAB'
      </USQL>
      <USQL exec="yes">
      update "HR"."T_NOCASCADE_FORCE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAT4MAAEAAAAInAAA'
      </USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
      <TRANSACTION XID="0400160012060000">
      <CHARACTERISTICS>
      </CHARACTERISTICS>
      <UNDO_SQL>
         <USQL exec="no">
         update "HR"."T_NOCASCADE_FORCE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAAT4MAAEAAAAInAAC'
         </USQL>
         <USQL exec="no">
         update "HR"."T_NOCASCADE_FORCE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAAT4MAAEAAAAInAAB'
         </USQL>
      </UNDO_SQL>
      <DEPENDENT_XIDS>
         <TRANSACTION XID="09000400B7060000">
         <CHARACTERISTICS>
         </CHARACTERISTICS>
         <UNDO_SQL>
            <USQL exec="no">
               update "HR"."T_NOCASCADE_FORCE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAAT4MAAEAAAAInAAB'
            </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 = 'AAAT4MAAEAAAAInAAC'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_NOCASCADE_FORCE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAT4MAAEAAAAInAAB'
</EXEC_USQL>
<EXEC_USQL>update "HR"."T_NOCASCADE_FORCE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAT4MAAEAAAAInAAA'
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>
------------
commit;
SQL> select * from t_nocascade_force;

ID
----------
   1
   2
   333

----------------------------------------------------------------------------------------------------

select ftq.xid,ftq.commit_scn ,ftq.operation ,ftq.undo_sql
from flashback_transaction_query ftq
where ftq.table_owner='HR'
and ftq.table_name='T_NONCONFLICT_ONLY'
order by ftq.commit_scn ;

复制代码

	XID	COMMIT_SCN	OPERATION	UNDO_SQL
1	0A002000C5050000	2384313	INSERT	delete from "HR"."T_NONCONFLICT_ONLY" where ROWID = 'AAAT4NAAEAAAAIvAAC';
2	0A002000C5050000	2384313	INSERT	delete from "HR"."T_NONCONFLICT_ONLY" where ROWID = 'AAAT4NAAEAAAAIvAAA';
3	0A002000C5050000	2384313	INSERT	delete from "HR"."T_NONCONFLICT_ONLY" where ROWID = 'AAAT4NAAEAAAAIvAAB';
4	0100160015060000	2384318	UPDATE	update "HR"."T_NONCONFLICT_ONLY" set "ID" = '2' where ROWID = 'AAAT4NAAEAAAAIvAAB';
5	0100160015060000	2384318	UPDATE	update "HR"."T_NONCONFLICT_ONLY" set "ID" = '1' where ROWID = 'AAAT4NAAEAAAAIvAAA';
6	0100160015060000	2384318	UPDATE	update "HR"."T_NONCONFLICT_ONLY" set "ID" = '3' where ROWID = 'AAAT4NAAEAAAAIvAAC';
7	0300160094060000	2384322	UPDATE	update "HR"."T_NONCONFLICT_ONLY" set "ID" = '33' where ROWID = 'AAAT4NAAEAAAAIvAAC';
8	0300160094060000	2384322	UPDATE	update "HR"."T_NONCONFLICT_ONLY" set "ID" = '22' where ROWID = 'AAAT4NAAEAAAAIvAAB';
9	040014001C060000	2384325	UPDATE	update "HR"."T_NONCONFLICT_ONLY" set "ID" = '222' where ROWID = 'AAAT4NAAEAAAAIvAAB';

begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('0100160015060000'),
options => dbms_flashback.nonconflict_only);
end;
select * from dba_flashback_txn_report;

复制代码

	COMPENSATING_XID	COMPENSATING_TXN_NAME	COMMIT_TIME	XID_REPORT	USERNAME
1	06002100C8060000		8/10/2018 8:08:13 PM	<CLOB>	SYS
2	02000700B0060000	_SYS_COMP_TXN_13173587_TIM_1533903232	8/10/2018 8:18:57 PM	<CLOB>	SYS
3	02001700B1060000	_SYS_COMP_TXN_13173587_TIM_1533904147		<CLOB>	SYS

CLOB：
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="02001700B1060000" NAME="_SYS_COMP_TXN_13173587_TIM_1533904147">
<TRANSACTION XID="0100160015060000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
      <USQL exec="no">
      update "HR"."T_NONCONFLICT_ONLY" set "ID" = '3' where "ID" = '33' and ROWID = 'AAAT4NAAEAAAAIvAAC'
      </USQL>
      <USQL exec="no">
      update "HR"."T_NONCONFLICT_ONLY" set "ID" = '2' where "ID" = '22' and ROWID = 'AAAT4NAAEAAAAIvAAB'
      </USQL>
      <USQL exec="yes">
      update "HR"."T_NONCONFLICT_ONLY" set "ID" = '1' where "ID" = '11' and ROWID = 'AAAT4NAAEAAAAIvAAA'
      </USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
      <TRANSACTION XID="0300160094060000">
      <CHARACTERISTICS>
      </CHARACTERISTICS>
      <UNDO_SQL>
         <USQL exec="no">
         update "HR"."T_NONCONFLICT_ONLY" set "ID" = '33' where "ID" = '333' and ROWID = 'AAAT4NAAEAAAAIvAAC'
         </USQL>
         <USQL exec="no">
         update "HR"."T_NONCONFLICT_ONLY" set "ID" = '22' where "ID" = '222' and ROWID = 'AAAT4NAAEAAAAIvAAB'
         </USQL>
      </UNDO_SQL>
      <DEPENDENT_XIDS>
         <TRANSACTION XID="040014001C060000">
         <CHARACTERISTICS>
         </CHARACTERISTICS>
         <UNDO_SQL>
            <USQL exec="no">
               update "HR"."T_NONCONFLICT_ONLY" set "ID" = '222' where "ID" = '22' and ROWID = 'AAAT4NAAEAAAAIvAAB'
            </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 = 'AAAT4NAAEAAAAIvAAA'
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>
----------------------------------------------------------------------------------------------------------------------
commit;
SQL> select * from t_nonconflict_only;

ID
----------
   1
22
   333
----------------------------------------------------------------------------------------------------

t05310simple.sql (344 Bytes, 下载次数: 337)

----------------------------------------------------------主键约束依赖关系：

SQL> create table t_parent( a number primary key ) ;
Table created.
SQL> insert into t_parent values (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> delete from t_parent where a=1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> insert into t_parent values (1) ;
1 row created.
SQL> commit;
Commit complete.

复制代码

select ftq.xid,ftq.commit_scn ,ftq.operation ,ftq.undo_sql
from flashback_transaction_query ftq
where ftq.table_owner='HR'
and ftq.table_name='T_PARENT'
order by ftq.commit_scn ;
begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('02000700B3060000'),
options => dbms_flashback.nocascade_force);
end;

复制代码

	XID	COMMIT_SCN	OPERATION	UNDO_SQL
1	070017000B060000	2389627	INSERT	delete from "HR"."T_PARENT" where ROWID = 'AAAT4QAAEAAAAJHAAA';
2	02000700B3060000	2389635	DELETE	insert into "HR"."T_PARENT"("A") values ('1');
3	07001B000B060000	2389663	INSERT	delete from "HR"."T_PARENT" where ROWID = 'AAAT4QAAEAAAAJHAAB';

begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('02000700B3060000'),
options => dbms_flashback.nonconflict_only);
end;
select * from dba_flashback_txn_report;

复制代码

	COMPENSATING_XID	COMPENSATING_TXN_NAME	COMMIT_TIME	XID_REPORT	USERNAME
1	06002100C8060000		8/10/2018 8:08:13 PM	<CLOB>	SYS
2	02000700B0060000	_SYS_COMP_TXN_13173587_TIM_1533903232	8/10/2018 8:18:57 PM	<CLOB>	SYS
3	02001700B1060000	_SYS_COMP_TXN_13173587_TIM_1533904147	8/10/2018 8:34:23 PM	<CLOB>	SYS
4	0100020017060000	_SYS_COMP_TXN_13173587_TIM_1533904695	8/10/2018 8:39:03 PM	<CLOB>	SYS
5	09001E00B5060000	_SYS_COMP_TXN_13173587_TIM_1533904855	8/10/2018 8:41:38 PM	<CLOB>	SYS
6	06001400CD060000			<CLOB>	SYS

CLOB：
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="06001400CD060000">
<TRANSACTION XID="02000700B3060000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
      <USQL exec="no">
      insert into "HR"."T_PARENT"("A") values ('1')
      </USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
      <TRANSACTION XID="07001B000B060000">
      <CHARACTERISTICS>
      </CHARACTERISTICS>
      <UNDO_SQL>
         <USQL exec="no">
         delete from "HR"."T_PARENT" where "A" = '1'
         </USQL>
      </UNDO_SQL>
      <DEPENDENT_XIDS>
      </DEPENDENT_XIDS>
      </TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>

------------------------------------------

begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('02000700B3060000'),
options => dbms_flashback.cascade);
end;
select * from dba_flashback_txn_report;

复制代码

	COMPENSATING_XID	COMPENSATING_TXN_NAME	COMMIT_TIME	XID_REPORT	USERNAME
1	06002100C8060000		8/10/2018 8:08:13 PM	<CLOB>	SYS
2	02000700B0060000	_SYS_COMP_TXN_13173587_TIM_1533903232	8/10/2018 8:18:57 PM	<CLOB>	SYS
3	02001700B1060000	_SYS_COMP_TXN_13173587_TIM_1533904147	8/10/2018 8:34:23 PM	<CLOB>	SYS
4	0100020017060000	_SYS_COMP_TXN_13173587_TIM_1533904695	8/10/2018 8:39:03 PM	<CLOB>	SYS
5	09001E00B5060000	_SYS_COMP_TXN_13173587_TIM_1533904855	8/10/2018 8:41:38 PM	<CLOB>	SYS
6	09000F00B5060000	_SYS_COMP_TXN_13173587_TIM_1533906837		<CLOB>	SYS

CLOB：
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="09000F00B5060000" NAME="_SYS_COMP_TXN_13173587_TIM_1533906837">
<TRANSACTION XID="02000700B3060000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
      <USQL exec="yes">
      insert into "HR"."T_PARENT"("A") values ('1')
      </USQL>
</UNDO_SQL>
<DEPENDENT_XIDS>
      <TRANSACTION XID="07001B000B060000">
      <CHARACTERISTICS>
      </CHARACTERISTICS>
      <UNDO_SQL>
         <USQL exec="yes">
         delete from "HR"."T_PARENT" where "A" = '1'
         </USQL>
      </UNDO_SQL>
      <DEPENDENT_XIDS>
      </DEPENDENT_XIDS>
      </TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>delete from "HR"."T_PARENT" where "A" = '1'
</EXEC_USQL>
<EXEC_USQL>insert into "HR"."T_PARENT"("A") values ('1')
</EXEC_USQL>
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>

select ftq.xid,ftq.commit_scn ,ftq.operation ,ftq.undo_sql
from flashback_transaction_query ftq
where ftq.table_owner='HR'
and ftq.table_name='T_PARENT'
order by ftq.commit_scn ;

复制代码

	XID	COMMIT_SCN	OPERATION	UNDO_SQL
1	070017000B060000	2389627	INSERT	delete from "HR"."T_PARENT" where ROWID = 'AAAT4QAAEAAAAJHAAA';
2	02000700B3060000	2389635	DELETE	insert into "HR"."T_PARENT"("A") values ('1');
3	07001B000B060000	2389663	INSERT	delete from "HR"."T_PARENT" where ROWID = 'AAAT4QAAEAAAAJHAAB';
4	09000F00B5060000		DELETE	insert into "HR"."T_PARENT"("A") values ('1');
5	09000F00B5060000		INSERT	delete from "HR"."T_PARENT" where ROWID = 'AAAT4QAAEAAAAJDAAA';

-------------------------------------------------------------------------------------------------------------------------------------------------------------外键约束依赖关系：

SQL> create table t_p ( a number primary key ) ;
Table created.
SQL> insert into t_p VALUES (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> create table t_s ( a number referencing t_p ) ;
Table created.
SQL> insert into t_s values ( 2);
insert into t_s values ( 2)
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.SYS_C0015123) violated - parent key not
found
SQL> insert into t_s values (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL>

复制代码

select ftq.xid,ftq.commit_scn ,ftq.operation ,ftq.undo_sql,ftq.table_name
from flashback_transaction_query ftq
where ftq.table_owner='HR'
and ftq.table_name in ('T_P','T_S')
order by ftq.commit_scn ;
begin
dbms_flashback.transaction_backout(numtxns => 2,
xids => xid_array('07000B000D060000','0400110021060000'),
options => dbms_flashback.cascade);
end;

复制代码

由于没有外建补充日志：

alter database add supplemental log data (foreign key) columns;

复制代码

SQL> create table t_p2 ( a number primary key ) ;
Table created.
SQL> insert into t_p2 VALUES (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> create table t_s2 ( a number referencing t_p2 );
Table created.
SQL> insert into t_s2 values (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL>

复制代码

select ftq.xid,ftq.commit_scn ,ftq.operation ,ftq.undo_sql,ftq.table_name
from flashback_transaction_query ftq
where ftq.table_owner='HR'
and ftq.table_name in ('T_P2','T_S2')
order by ftq.commit_scn ;

复制代码

	XID	COMMIT_SCN	OPERATION	UNDO_SQL	TABLE_NAME
1	08000F0037060000	2394322	INSERT	delete from "HR"."T_P2" where ROWID = 'AAAT4VAAEAAAAJ/AAA';	T_P2
2	03000E009B060000	2394396	INSERT	delete from "HR"."T_S2" where ROWID = 'AAAT4XAAEAAAAKHAAA';	T_S2

1 select ftq.xid,ftq.commit_scn ,ftq.operation ,ftq.undo_sql,ftq.table_name
2 from flashback_transaction_query ftq
3 where ftq.table_owner='HR'
4 and ftq.table_name in ('T_P','T_S')
5* order by ftq.commit_scn
SQL> /
XID COMMIT_SCN OPERATION UNDO_SQL TABLE_NAME
---------------- ---------- -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
04000E0005120000 4977399 INSERT delete from "HR"."T_P" where ROWID = 'AAAoBkAAEAAAAC1AAA'; T_P
0C0011002A090000 4977585 INSERT delete from "HR"."T_S" where ROWID = 'AAAoBmAAEAAAADFAAA'; T_S
begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('04000E0005120000'),
options => dbms_flashback.cascade);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> set long 10000
SQL> select * from dba_flashback_txn_report;
COMPENSATING_XID COMPENSATING_TXN_NAME COMMIT_TIME XID_REPORT USERNAME
---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
0900180015120000 _SYS_COMP_TXN_75674175_TIM_1533908729 <?xml version="1.0" encoding="ISO-8859-1"?> SYS
<COMP_XID_REPORT XID="0900180015120000" NAME="_SYS_COMP_
TXN_75674175_TIM_1533908729">
<TRANSACTION XID="04000E0005120000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL exec="yes">
delete from "HR"."T_P" where "A" = '1'
</USQL>
COMPENSATING_XID COMPENSATING_TXN_NAME COMMIT_TIME XID_REPORT USERNAME
---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
</UNDO_SQL>
<DEPENDENT_XIDS>
<TRANSACTION XID="0C0011002A090000">
<CHARACTERISTICS>
</CHARACTERISTICS>
<UNDO_SQL>
<USQL exec="yes">
delete from "HR"."T_S" where "A" = '1' a
nd ROWID = 'AAAoBmAAEAAAADFAAA'
</USQL>
</UNDO_SQL>
COMPENSATING_XID COMPENSATING_TXN_NAME COMMIT_TIME XID_REPORT USERNAME
---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
<DEPENDENT_XIDS>
</DEPENDENT_XIDS>
</TRANSACTION>
</DEPENDENT_XIDS>
</TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>delete from "HR"."T_S" where "A" = '1' an
d ROWID = 'AAAoBmAAEAAAADFAAA'
</EXEC_USQL>
<EXEC_USQL>delete from "HR"."T_P" where "A" = '1'
</EXEC_USQL>
COMPENSATING_XID COMPENSATING_TXN_NAME COMMIT_TIME XID_REPORT USERNAME
---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>
SQL>

复制代码

路过

雷人

握手

鲜花

鸡蛋

收藏邀请

上一篇：【博客文章2022】同时包含有效时间维度和事务历史时间维度的bi-temporal查询