定位表的最后操作时间
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功能都会记录对表进行的最后操作时间。
补充：综合编程 , 其他综合 ,
