监控表的更新信息

通过view:all_tab_modifications 来监控表的更新信息.........

View:all_tab_modifications

PARTITION_NAME	VARCHAR2(30)		Name of the modified partition
SUBPARTITION_NAME	VARCHAR2(30)		Name of the modified subpartition
INSERTS	NUMBER		Approximate number of inserts since the last time statistics were gathered
UPDATES	NUMBER		Approximate number of updates since the last time statistics were gathered
DELETES	NUMBER		Approximate number of deletes since the last time statistics were gathered
TIMESTAMP	DATE		Indicates the last time the table was modified
TRUNCATED	VARCHAR2(3)		Whether or not the table was truncated since the last time statistics were gathered

使得all_tab_modifications有数据反映,不同的版本有所区别

SQL> create table test as select * from emp;

Table created.

SQL> desc test;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NUMBER
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER
HIREDATE DATE
SAL NUMBER
COMM NUMBER
DEPETNO NUMBER(2)

SQL> select table_name,monitoring
2 from all_tables
3 where table_name='TEST';

TABLE_NAME MON
------------------------------ ---
TEST NO

SQL> alter table test monitoring;

Table altered.

SQL> select table_name,monitoring
2 from all_tables
3 where table_name='TEST';

TABLE_NAME MON
------------------------------ ---
TEST YES

SQL> select * from all_tab_modifications;

no rows selected

SQL> delete from test where rownum = 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from all_tab_modifications;

no rows selected

SQL> exec dbms_stats.flush_database_monitoring_info();

PL/SQL procedure successfully completed.

SQL> select * from all_tab_modifications;

TABLE_OWNER TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU
------------------------------ ---------- ---------- ---------- ------------------- ---
TEST TEST
0 0 1 2006-06-30 17:44:15 NO

SQL> update test set ename=upper(ename);

13 rows updated.

SQL> commit;

Commit complete.

SQL> select * from all_tab_modifications;

TABLE_OWNER TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU
------------------------------ ---------- ---------- ---------- ------------------- ---
TEST TEST
0 0 1 2006-06-30 17:44:15 NO

SQL> exec dbms_stats.flush_database_monitoring_info();

PL/SQL procedure successfully completed.

SQL> select * from all_tab_modifications;

TABLE_OWNER TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU
------------------------------ ---------- ---------- ---------- ------------------- ---
TEST TEST
0 13 1 2006-06-30 17:57:06 NO

. wait 3 hours or so with 8i
. use dbms_stats to flush modifications at will in 9i
. shutdown/startup cleanly in any release

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45259/viewspace-134879/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45259/viewspace-134879/