[20150828]插入commit scn到記錄.txt

[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的語句.