查看表最后DML和DDL时间

SQL> create table uuu as select * from emp;

表已创建。

SQL> select ora_rowscn,t.* from uuu t;

ORA_ROWSCN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
3183031 7369 SMITH CLERK 7902 17-12月-80 800 20
3183031 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
3183031 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
3183031 7566 JONES MANAGER 7839 02-4月 -81 2975 20
3183031 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
3183031 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
3183031 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
3183031 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
3183031 7839 KING PRESIDENT 17-11月-81 5000 10
3183031 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
3183031 7876 ADAMS CLERK 7788 23-5月 -87 1100 20

ORA_ROWSCN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
3183031 7900 JAMES CLERK 7698 03-12月-81 950 30
3183031 7902 FORD ANALYST 7566 03-12月-81 3000 20
3183031 7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择14行。

SQL> insert into uuu select * from uuu;

已创建14行。

SQL> commit;

提交完成。

SQL> select ora_rowscn,t.* from uuu t;

ORA_ROWSCN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
3183031 7369 SMITH CLERK 7902 17-12月-80 800 20
3183031 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
3183031 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
3183031 7566 JONES MANAGER 7839 02-4月 -81 2975 20
3183031 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
3183031 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
3183031 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
3183031 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
3183031 7839 KING PRESIDENT 17-11月-81 5000 10
3183031 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
3183031 7876 ADAMS CLERK 7788 23-5月 -87 1100 20

ORA_ROWSCN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
3183031 7900 JAMES CLERK 7698 03-12月-81 950 30
3183031 7902 FORD ANALYST 7566 03-12月-81 3000 20
3183031 7934 MILLER CLERK 7782 23-1月 -82 1300 10
3183153 7369 SMITH CLERK 7902 17-12月-80 800 20
3183153 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
3183153 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
3183153 7566 JONES MANAGER 7839 02-4月 -81 2975 20
3183153 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
3183153 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
3183153 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
3183153 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

ORA_ROWSCN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
3183153 7839 KING PRESIDENT 17-11月-81 5000 10
3183153 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
3183153 7876 ADAMS CLERK 7788 23-5月 -87 1100 20
3183153 7900 JAMES CLERK 7698 03-12月-81 950 30
3183153 7902 FORD ANALYST 7566 03-12月-81 3000 20
3183153 7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择28行。

SQL>

查看最后DDL时间

SQL> SELECT TO_CHAR(LAST_DDL_TIME,'YYYY-MM-DD HH24:MI:SS') FROM DBA_OBJECTS WHERE OBJECT_NAME='UUU';

TO_CHAR(LAST_DDL_TI
-------------------
2014-05-06 23:07:40

查看DML时间，根据ORA_ROWSCN
SQL> select scn_to_timestamp(3183153) from dual;

SCN_TO_TIMESTAMP(3183153)
---------------------------------------------------------------------------
06-5月 -14 11.08.16.000000000 下午

SQL>

当然每行的SCN可能是不一样的，对伪列求个max就好.