SCN、TIMESTAMP那点儿事

一、sqlplus显示时间

1、开启sqlplus命令行时间显示

SQL>

SQL> SET TIME ON

17:52:51 SQL>

17:52:53 SQL>

2 、关闭sqlplus命令行时间显示

17:52:59 SQL>

17:53:00 SQL> SET TIME OFF

SQL>

二、获取SCN

1、获取数据库当前的SCN

SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN

FROM DUAL;

       SCN

------------------

   3097978

SQL> SELECT CURRENT_SCN SCN FROM  V$DATABASE;

       SCN

--------------------

    3097997

2、获取控制文件内标识的SCN

SQL> select checkpoint_change#,file#,name from v$datafile;

CHECKPOINT_CHANGE#      FILE#      NAME

-------------------------------------- ---------- ------------------------------------------------------------

           3096167           1 /oradata/mydata/system01.dbf

           3096167         2 /oradata/mydata/undotbs01.dbf

           3096167         3  /oradata/mydata/sysaux01.dbf

           3096167           4  /oradata/mydata/users01.dbf

           3096167           5  /oradata/mydata/example01.dbf

           3096167           6  /oradata/mydata/mydata001.dbf

           3096167           7  /oradata/mydata/mydata002.dbf

           3096167           8  /oradata/mydata/mydata003.dbf

8 rows selected.

3、获取数据文件头中的SCN

SQL> s elect checkpoint_change#,file#,name from v$datafile_header;

CHECKPOINT_CHANGE#      FILE#         NAME

-------------------------------------- ---------- ------------------------------------------------------------

           3096167           1 /oradata/mydata/system01.dbf

           3096167           2 /oradata/mydata/undotbs01.dbf

           3096167           3 /oradata/mydata/sysaux01.dbf

           3096167           4 /oradata/mydata/users01.dbf

           3096167           5 /oradata/mydata/example01.dbf

           3096167           6 /oradata/mydata/mydata001.dbf

           3096167           7 /oradata/mydata/mydata002.dbf

           3096167           8 /oradata/mydata/mydata003.dbf

8 rows selected.

三、转换

1、将时间转为TIMESTAMP

18:04:04 SQL> SELECT  TO_TIMESTAMP('2011-03-26 18:04:04','yyyy-mm-dd hh24:mi:ss')  TIMESTAMP FROM DUAL;

TIMESTAMP

---------------------------------------------------------------------------

26-MAR-11 06.04.04.000000000 PM

18:04:33 SQL>

2、将SCN转为TIMESTAMP

SQL> SELECT SCN_TO_TIMESTAMP(3099139) TIMESTAMP FROM DUAL;

TIMESTAMP

---------------------------------------------------------------------------

26-MAR-11 06.05.54.000000000 PM

3、将TIMESTAMP转为SCN

SQL> SELECT TIMESTAMP_TO_SCN(to_timestamp('26-MAR-11 06:05:54 PM')) SCN FROM DUAL;

       SCN

----------

   3099138

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25264937/viewspace-690584/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25264937/viewspace-690584/