由於時間久，資料庫中的表太多，前後很多人操作過，也不知道哪些表有用哪些沒用，於是，想透過判斷資料表的最後DML時間，來確定哪些資料表最近沒操作過。

SQL> create table A as select id,name from t_employee;

Table created

SQL> select tb.table_name,tb.monitoring from user_tables tb where table_name='A';

TABLE_NAME MONITORING
------------------------------ ----------
A YES

由此可以看到，資料表預設都是啟動了monitoring功能的。

下面，透過ORA_ROWSCN來定位表A的最後dml時間。

SQL> select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) from mvs.A;

MAX(ORA_ROWSCN) SCN_TO_TIMESTAMP(MAX(ORA_ROWSC
--------------- --------------------------------------------------------------------------------
155220760 29-11月-11 11.25.50.000000000 上午

有人說只要表為monitoring狀態，從檢視 user_tab_modifications 也可以看到，可實際上確什麼都沒查到。

SQL> select * from user_tab_modifications where table_name='A';

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- -------------

刪除A中的資料，只剩下一條。

SQL> select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) from mvs.A;

MAX(ORA_ROWSCN) SCN_TO_TIMESTAMP(MAX(ORA_ROWSC
--------------- --------------------------------------------------------------------------------
155223006 29-11月-11 11.46.33.000000000 上午

然後再插入一條記錄。

SQL> insert into a(id,name) values(1,'test');

1 row inserted

SQL> commit;

Commit complete

檢視記錄及對應的偽列ORA_ROWSCN值。

SQL> select id,name,ora_rowscn from a order by id;

        ID    NAME                 ORA_ROWSCN
----------   -------------------- ----------------------
         1     test                  155223032
1108     s11                   155223006

SQL>

透過上面的偽列 ORA_ROWSCN 及函式SCN_TO_TIMESTAMP(ORA_ROWSCN)就可以獲得該行資料的最後DML時間。

SQL> insert into a(id,name) values(2,'test');

1 row inserted

SQL> insert into a(id,name) values(3,'test');

1 row inserted

SQL> commit;

Commit complete

SQL> select id,name,ora_rowscn from a order by id;

        ID NAME                 ORA_ROWSCN
---------- -------------------- ----------
         1 test                  155226434
         2 test                  155226434
         3 test                  155226434
1108 s11                   155223006

SQL> insert into a(id,name) values(4,'test');

1 row inserted

SQL> commit;

Commit complete

SQL> select id,name,ora_rowscn from a order by id;

        ID NAME                 ORA_ROWSCN
---------- -------------------- ----------
         1 test                  155226448
         2 test                  155226448
         3 test                  155226448
         4 test                  155226448
1108 s11                   155223006

SQL> insert into a(id,name) values(5,'test2');

1 row inserted

SQL> commit;

Commit complete

SQL> select id,name,ora_rowscn from a order by id;

        ID NAME                 ORA_ROWSCN
---------- -------------------- ----------
         1 test                  155226463
         2 test                  155226463
         3 test                  155226463
         4 test                  155226463
         5 test2                 155226463
1108 s11                   155223006

6 rows selected

SQL>

Oracle中定位資料表的最近DML時間

相關文章