基於行跟蹤的ROWDEPENDENCIES實現資訊變化跟蹤

如何對oracle表中的資料發生修改時實現跟蹤，有以下幾種方法：

1.      基於觸發器： 當表中資料發生變化時，透過表上的觸發器來記錄修改的內容。這種方法是開發人員常見使用的方法。

2.  基於日誌的分析： 例如使用LogMiner或者Goldengate軟體，對日誌檔案進行分析，提取資料變化。這種方法不增加資料庫負擔，但配置稍麻煩，適合於DBA使用。

3.  ROWDEPENDENCIES 方法： 這是oracle10G 後引入的新特性，ORACLE 透過引入ORA_ROWSCN偽列新特性，我們可以方便地找出某個資料塊或某一個行最近被修改的時間戳。這也是本文介紹的重點內容。

 

透過scn_to_timestamp(ora_rowscn) ,顯示該行修改的時間，舉例如下：

SQL> select * from v$version ;

 BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0     Production

TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

 

 

SQL> CREATE TABLE ADT_LOG

  2  (

  3    WARD_CODE      VARCHAR2(8 BYTE),

  4    DEPT_CODE      VARCHAR2(8 BYTE),

  5    LOG_DATE_TIME  DATE,

  6    PATIENT_ID     VARCHAR2(10 BYTE),

  7    VISIT_ID       NUMBER(2),

  8    ACTION         VARCHAR2(1 BYTE)

  9  )  rowdependencies ;

 

Table created

插入第一條資料

 

SQL> insert into adt_log

  2    (WARD_CODE, DEPT_CODE, LOG_DATE_TIME, PATIENT_ID, VISIT_ID, ACTION)

  3  values

  4    ('0602',

  5     '030201',

  6     to_date('10-05-2001 16:47:22', 'dd-mm-yyyy hh24:mi:ss'),

  7     '9712277002',

  8     1,

  9     'F');

 

1 row inserted

SQL> commit;

 

Commit complete

SQL> select scn_to_timestamp(ora_rowscn) orscn, WARD_CODE from ADT_LOG;

 

ORSCN                                             WARD_CODE

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

04-MAY-15 03.13.20.000000000 PM                   0602

插入第二條資料

SQL> insert into adt_log

  2    (WARD_CODE, DEPT_CODE, LOG_DATE_TIME, PATIENT_ID, VISIT_ID, ACTION)

  3  values

  4    ('0602',

  5     '030201',

  6     to_date('10-05-2001 16:47:34', 'dd-mm-yyyy hh24:mi:ss'),

  7     '9707089371',

  8     1,

  9     'F');

 

1 row inserted

SQL> commit;

 

Commit complete

SQL> select scn_to_timestamp(ora_rowscn) orscn, WARD_CODE from ADT_LOG;

 

ORSCN                                             WARD_CODE

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

04-MAY-15 03.13.20.000000000 PM                   0602

04-MAY-15 03.14.04.000000000 PM                   0602

插入第三條資料

SQL> insert into adt_log

  2    (WARD_CODE, DEPT_CODE, LOG_DATE_TIME, PATIENT_ID, VISIT_ID, ACTION)

  3  values

  4    ('0607',

  5     '030702',

  6     to_date('10-05-2001 16:50:16', 'dd-mm-yyyy hh24:mi:ss'),

  7     '86377',

  8     1,

  9     'C');

 

1 row inserted

SQL> commit;

 

Commit complete

SQL> select scn_to_timestamp(ora_rowscn) orscn, WARD_CODE from ADT_LOG;

 

ORSCN                                             WARD_CODE

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

04-MAY-15 03.13.20.000000000 PM                   0602

04-MAY-15 03.14.04.000000000 PM                   0602

04-MAY-15 03.18.35.000000000 PM                   0607

 

 

修改第三條記錄：

SQL> update adt_log set log_date_time = sysdate where ward_code = '0607';

 

1 row updated

SQL> commit;

 

Commit complete

SQL> select scn_to_timestamp(ora_rowscn) orscn, WARD_CODE from ADT_LOG;

 

ORSCN                                             WARD_CODE

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

04-MAY-15 03.13.20.000000000 PM                   0602

04-MAY-15 03.14.04.000000000 PM                   0602

04-MAY-15 03.21.29.000000000 PM                   0607

需說明的是，如果對於DELETE 的行，恐怕不能顯示出來，如果要顯示出來，只能使用快照查詢，例如：

SQL> delete from  ADT_LOG  where WARD_CODE = '0607';

 

1 row deleted

SQL> commit;

 

Commit complete

SQL> select scn_to_timestamp(ora_rowscn) orscn, WARD_CODE  from ADT_LOG;

 

ORSCN                                             WARD_CODE

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

04-MAY-15 03.26.50.000000000 PM                   0602

04-MAY-15 03.26.50.000000000 PM                   0602

SQL> select scn_to_timestamp(ora_rowscn) orscn, WARD_CODE

  2    from ADT_LOG AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' MINUTE);

 

ORSCN                                             WARD_CODE

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

04-MAY-15 03.26.50.000000000 PM                   0602

04-MAY-15 03.26.50.000000000 PM                   0602

04-MAY-15 03.26.50.000000000 PM                   0607

需要注意的是ROWDEPENDENCIES屬性只有在建立表時使用才有效，對於已有的表，透過alter table 語句是無效的，因此需透過重建表的方式來解決。

SQL> RENAME ADT_LOG TO ADT_LOG_1;

 

Table renamed

 

SQL>

SQL> CREATE TABLE ADT_LOG

  2  (

  3    WARD_CODE      VARCHAR2(8 BYTE),

  4    DEPT_CODE      VARCHAR2(8 BYTE),

  5    LOG_DATE_TIME  DATE,

  6    PATIENT_ID     VARCHAR2(10 BYTE),

  7    VISIT_ID       NUMBER(2),

  8    ACTION         VARCHAR2(1 BYTE)

  9  )  rowdependencies ;

 

Table created

SQL> INSERT INTO ADT_LOG

  2  select * from  ADT_LOG_1;

 

3 rows inserted

SQL> commit;

 

Commit complete

SQL> drop table ADT_LOG_1 purge ;

 

Table dropped

 

SQL> select scn_to_timestamp(ora_rowscn) orscn, WARD_CODE  from ADT_LOG;

 

ORSCN                                             WARD_CODE

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

04-MAY-15 03.26.50.000000000 PM                   0602

04-MAY-15 03.26.50.000000000 PM                   0602

04-MAY-15 03.26.50.000000000 PM                   0607