一、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> select 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博客 ” ，链接：https://blog.itpub.net/25264937/viewspace-690584/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
配置RHEL AS4.8的VNC远程桌面
下一篇：
Flahsback database
请登录后发表评论
登录
全部评论
