View:all_tab_modifications

使得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