[20150828]插入commit scn到记录.txt
--昨天看
--链接:http://blog.dbi-services.com/oracle-cdc-for-datawarehouse-dbvisit-replicate-as-an-alternative/
--发现通过使用函数userenv('commitscn'),可以实现在DML记录(插入与修改)时记录提交scn的功能,自己按照例子也做了测试!
1.建立测试环境:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> create table DEMO (id number, update_scn number, commit_scn number);
Table created.
SCOTT@test01p> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
20498673
2.开始测试:
SCOTT@test01p> insert into DEMO values (1,dbms_flashback.get_system_change_number,userenv('commitscn'));
1 row created.
SCOTT@test01p> select * from demo;
ID UPDATE_SCN COMMIT_SCN
---------- ---------- ----------
1 20498680 20498680
-- 插入时UPDATE_SCN=COMMIT_SCN.
SCOTT@test01p> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
20498693
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select * from demo;
ID UPDATE_SCN COMMIT_SCN
---------- ---------- ----------
1 20498680 20498695
--可以发现commit后,字段commit_scn的信息发生了变化,感觉就像给commit命令加了触发器,修改了提交信息.
SCOTT@test01p> select ora_rowscn,demo.* from demo ;
ORA_ROWSCN ID UPDATE_SCN COMMIT_SCN
---------- ---------- ---------- ----------
20498696 1 20498680 20498695
-- 伪列ora_rowscn 与 commit_scn 相差1 .
3.修改记录看看:
SCOTT@test01p> update demo set commit_scn=userenv('commitscn');
1 row updated.
SCOTT@test01p> select ora_rowscn,demo.* from demo ;
ORA_ROWSCN ID UPDATE_SCN COMMIT_SCN
---------- ---------- ---------- ----------
20498696 1 20498680 20498827
--commit_scn再次发生了变化.
SCOTT@test01p> rollback ;
Rollback complete.
SCOTT@test01p> select ora_rowscn,demo.* from demo ;
ORA_ROWSCN ID UPDATE_SCN COMMIT_SCN
---------- ---------- ---------- ----------
20498696 1 20498680 20498695
--回滚后还原.下面修改并提交看看.
SCOTT@test01p> update demo set commit_scn=userenv('commitscn');
1 row updated.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select ora_rowscn,demo.* from demo ;
ORA_ROWSCN ID UPDATE_SCN COMMIT_SCN
---------- ---------- ---------- ----------
20498854 1 20498680 20498853
--感觉有点奇怪,oracle内部如何实现这个功能的?
4.使用logminer探究看看.
BEGIN
DBMS_LOGMNR.START_LOGMNR (
STARTSCN => 20498673,
ENDSCN => 20498854,
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
+ DBMS_LOGMNR.CONTINUOUS_MINE
+ DBMS_LOGMNR.COMMITTED_DATA_ONLY);
END;
/
SYS@test> column SQL_REDO format a100
SYS@test> select scn,start_scn,commit_scn,seg_owner,operation,sql_redo from v$logmnr_contents where seg_owner='SCOTT';
SCN START_SCN COMMIT_SCN SEG_OWNER OPERATION SQL_REDO
---------- ---------- ---------- ---------- ------------ -----------------------------------------------------------------------------------------------
20498682 20498682 20498696 SCOTT INSERT insert into "SCOTT"."DEMO"("ID","UPDATE_SCN","COMMIT_SCN") values ('1','20498680','20498680');
20498695 20498682 20498696 SCOTT UNSUPPORTED Unsupported
--看不到修改语句.没有打开SUPPLEMENTAL LOG DATA;
SYS@test> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
5.重复测试:
SCOTT@test01p> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
20535870
SCOTT@test01p> insert into DEMO values (2,dbms_flashback.get_system_change_number,userenv('commitscn'));
1 row created.
SCOTT@test01p> select ora_rowscn,demo.* from demo ;
ORA_ROWSCN ID UPDATE_SCN COMMIT_SCN
---------- ---------- ---------- ----------
20498854 1 20498680 20498853
20498682 2 20535883 20535883
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select ora_rowscn,demo.* from demo ;
ORA_ROWSCN ID UPDATE_SCN COMMIT_SCN
---------- ---------- ---------- ----------
20498854 1 20498680 20498853
20535907 2 20535883 20535906
6.再次使用logminer探究看看.
BEGIN
DBMS_LOGMNR.START_LOGMNR (
STARTSCN => 20535870,
ENDSCN => 20535907,
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
+ DBMS_LOGMNR.CONTINUOUS_MINE
+ DBMS_LOGMNR.COMMITTED_DATA_ONLY);
END;
/
SYS@test> select scn,start_scn,commit_scn,seg_owner,operation,sql_redo from v$logmnr_contents where seg_owner='SCOTT';
SCN START_SCN COMMIT_SCN SEG_OWNER OPERATION SQL_REDO
---------- ---------- ---------- ---------- ---------- ------------------------------------------------------------------------------------------------------------------------
20535883 20535883 20535907 SCOTT INSERT insert into "SCOTT"."DEMO"("ID","UPDATE_SCN","COMMIT_SCN") values ('2','20535883','20535883');
20535906 20535883 20535907 SCOTT UPDATE update "SCOTT"."DEMO" set "COMMIT_SCN" = '20535906' where "COMMIT_SCN" = '20535883' and ROWID = 'AAAYBeAAJAAAAC+AAA';
--确实可以看到在提交时有一个修改commit_scn的语句.