定位表的最后操作时间

1.创建测试表HAT_OBJECTS
SQL> CREATE TABLE HAT_OBJECTS AS SELECT * FROM ALL_OBJECTS;
 
Table created
 
2.查看表HAT_OBJECTS的MONITORING功能是否打开(默认是打开的)
SQL> SELECT T.TABLE_NAME,T.MONITORING FROM USER_TABLES T WHERE

T.TABLE_NAME='HAT_OBJECTS';
 
TABLE_NAME                     MONITORING
------------------------------ ----------
HAT_OBJECTS                    YES
 
3.查看表HAT_OBJECTS最后的操作时间
SQL> SELECT MAX(ORA_ROWSCN) AS MAXROWSCN,TO_CHAR(scn_to_timestamp(MAX(ORA_ROWSCN)),'YYYY

-MM-DD HH24:MI:SS') AS NEWTIME FROM HAT_OBJECTS;
 
 MAXROWSCN NEWTIME
---------- -------------------
    345550 2012-12-07 13:45:51
 
4.对表HAT_OBJECTS进行DDL操作
SQL> ALTER TABLE HAT_OBJECTS DROP COLUMN SECONDARY;
 
Table altered
 
SQL> SELECT MAX(ORA_ROWSCN) AS MAXROWSCN,TO_CHAR(scn_to_timestamp(MAX(ORA_ROWSCN)),'YYYY

-MM-DD HH24:MI:SS') AS DDLNEWTIME FROM HAT_OBJECTS;
 
 MAXROWSCN DDLNEWTIME
---------- -------------------
    346523 2012-12-07 13:57:15

5.对表HAT_OBJECTS进行DML操作
SQL> SELECT COUNT(*) FROM HAT_OBJECTS;
 
  COUNT(*)
----------
     40680
 
SQL> DELETE FROM HAT_OBJECTS WHERE ROWID IN (SELECT ROWID FROM HAT_OBJECTS WHERE

ROWNUM<=10);
 
10 rows deleted
 
SQL> COMMIT;
 
Commit complete
 
SQL> SELECT COUNT(*) FROM HAT_OBJECTS;
 
  COUNT(*)
----------
     40670
 
SQL> SELECT MAX(ORA_ROWSCN) AS MAXROWSCN,TO_CHAR(scn_to_timestamp(MAX(ORA_ROWSCN)),'YYYY

-MM-DD HH24:MI:SS') AS DMLNEWTIME FROM HAT_OBJECTS;
 
 MAXROWSCN DMLNEWTIME
---------- -------------------
    346927 2012-12-07 14:03:18

6.总结
无论对表是进行DML操作，还是DDL操作，ORACLE的MONITORING功能都会记录对表进行的最后操作时间。