课程第26次
Notice: This blog is written by Bo Tang.$ 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_cascade3values (1)
1 row created.
old 1: insert into &&s1 values (2)
new 1: insert into t_cascade3values (2)
1 row created.
old 1: insert into &&s1 values (3)
new 1: insert into t_cascade3values (3)
1 row created.
Commit complete.
old 1: update &&s1 set id=11 where id=1
new 1: update t_cascade3set id=11 where id=1
1 row updated.
old 1: update &&s1 set id=22 where id=2
new 1: update t_cascade3set id=22 where id=2
1 row updated.
old 1: update &&s1 set id=33 where id=3
new 1: update t_cascade3set id=33 where id=3
1 row updated.
Commit complete.
old 1: update &&s1 set id=222 where id=22
new 1: update t_cascade3set id=222 where id=22
1 row updated.
old 1: update &&s1 set id=333 where id=33
new 1: update t_cascade3set id=333 where id=33
1 row updated.
Commit complete.
old 1: update &&s1 set id=22 where id=222
new 1: update t_cascade3set id=22 where id=222
1 row updated.
Commit complete.
SQL>
selectlog_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 10Mretention 10 year;
grant flashback archive on fda1 to hr;
$ 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_NAMEFLASHBACK_ARCHIVE_NAMEFLASHBACK_ARCHIVE#RETENTION_IN_DAYSCREATE_TIMELAST_PURGE_TIMESTATUS
1SYSFDA11365023-10月-19 04.16.50.000000000 下午23-10月-19 04.16.50.000000000 下午
TABLE_NAMEOWNER_NAMEFLASHBACK_ARCHIVE_NAMEARCHIVE_TABLE_NAMESTATUS
1EMPLOYEESHRFDA1SYS_FBA_HIST_88047ENABLED
FLASHBACK_ARCHIVE_NAMEFLASHBACK_ARCHIVE#TABLESPACE_NAMEQUOTA_IN_MB
1FDA11USERS10
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 )
2partition 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_part1values (100);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t_part1values(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_part1partition(t_part1_p1);
A
----------
1
SQL> select* from t_part1partition(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_partitionstp where tp.table_owner='HR' and tp.table_name='T_PART1';
SQL> alter table t_part1 split partition t_part1_p1 at (50)into ( partitiont_part1_p11, partition t_part1_p12) ;
alter table t_part1 split partition t_part1_p1 at (50)into ( partitiont_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 ( partitiont_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;selectversions_xid, versions_operation, versions_startscn , afrom hr.t_part1
versions between scn minvalue and maxvalue;
页:
[1]