[20170310]V$RMAN_OUTPUT的stamp 2.txt
http://blog.itpub.net/267265/viewspace-1979123/
--//以前写的,今天才发现自己再次犯严重的错误.重新写一篇,实际上oracle的时间戳都是按照如下公式计算的:
time = (((((yyyy - 1988) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi) * 60 + ss;
--//oracle强行按每月31天来计算时间戳,实际上发现这个是因为2月才28天,我昨天发现到3月存在明显的跳跃.
--//重新验证看看:
1.环境:
SYS@book> @ &r/ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--建立如下脚本执行它：
$ cat aa.rman
# !/bin/bash
rman target / append <<EOF
host "date +'%Y/%m/%d %T'>> aa.log";
host "sleep 10";
host "date +'%Y/%m/%d %T'>> aa.log";
EOF
$ cat aa.log
2017/03/10 09:30:55
2017/03/10 09:31:05
SYS@book> select * from V$RMAN_OUTPUT where session_key=453;
SID RECID STAMP SESSION_RECID SESSION_STAMP OUTPUT RMAN_STATUS_RECID RMAN_STATUS_STAMP SESSION_KEY
--- ----- ---------- ------------- ------------- --------------------- ----------------- ----------------- -----------
232 53 938251855 453 938251855 453 938251855 453
232 54 938251855 453 938251855 453 938251855 453
232 55 938251855 453 938251855 host command complete 453 938251855 453
232 56 938251855 453 938251855 453 938251855 453
232 57 938251855 453 938251855 453 938251855 453
232 58 938251865 453 938251855 host command complete 453 938251855 453
232 59 938251865 453 938251855 453 938251855 453
232 60 938251865 453 938251855 453 938251855 453
232 61 938251865 453 938251855 host command complete 453 938251855 453
232 62 938251865 453 938251855 453 938251855 453
232 63 938251865 453 938251855 453 938251855 453
232 64 938251865 453 938251855 453 938251855 453
232 65 938251865 453 938251855 453 938251855 453
14 rows selected.
--//stamp=938251855,938251865,相差10,说明单位是秒.
$ cat stamp.sql
SELECT &&1 stamp,to_date(yyyy||'/'||mm||'/'||dd||' '||hh||':'||mi||':'||ss,'yyyy-mm-dd hh24:mi:ss') stamp_conv_time from (
SELECT &&1
,FLOOR (&&1 / (86400*31*12))+1988 yyyy
,FLOOR (MOD (&&1 / (86400*31),12))+1 mm
,FLOOR (MOD (&&1 / 86400, 31))+1 dd
,FLOOR (MOD (&&1 / 3600, 24)) hh
,FLOOR (MOD (&&1 / 60, 60)) mi
,MOD (&&1, 60) ss
from dual);
SYS@book> @ &r/stamp 938251855
STAMP STAMP_CONV_TIME
---------- -------------------
938251855 2017-03-10 09:30:55
SYS@book> @ &r/stamp 938251865
STAMP STAMP_CONV_TIME
---------- -------------------
938251865 2017-03-10 09:31:05
--//与aa.log文件内显示的时间一致.看来oracle内部time时间戳应该都是按照这个来计算的.特此更正.