查看表最后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> 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就好.
TO_CHAR(LAST_DDL_TI
-------------------
2014-05-06 23:07:40
SQL> select scn_to_timestamp(3183153) from dual;
SCN_TO_TIMESTAMP(3183153)
---------------------------------------------------------------------------
06-5月 -14 11.08.16.000000000 下午
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29477587/viewspace-1155484/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29477587/viewspace-1155484/