[20170309]关于v$archived_log.stamp字段.txt
--//如果你查询v$archived_log,可以看到一个stamp字段,它表示什么呢?根据以前的经验.这个类似从某个时间点计算的秒数.
--//参考链接:
http://blog.itpub.net/267265/viewspace-1979123/
--曾经探究过V$RMAN_OUTPUT 视图,STAMP 是从时间"1987-06-26 00:00:00"开始的.
--//简单探究这个v$archived_log.stamp从那个时间开始的.
1.环境:
select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
--//我的测试库归档太少,找一个10g的归档很多的数据库测试.
2.利用v$archived_log的next_time可以简单判断:
--//dest_id=1是本地归档,一般闪回区或者磁盘充足的,归档马上发生.
SELECT stamp
,sequence#
,first_time
,NEXT_TIME
,NEXT_TIME - stamp / 86400 begin_time
FROM v$archived_log
WHERE dest_id = 1 AND name IS NOT NULL
ORDER BY 1 DESC;
--//发现很奇怪的现象,NEXT_TIME - stamp / 86400范围在1987/6/17-1987/6/20.起点应该一致的.很明显不是这样运算的.
--//想起http://www.juliandyke.com/Diagnostics/Dumps/RedoLogs.php转储redo时time参数使用:
TIME
The minimum and maximum time is a decimal number representing the number of seconds since midnight 01Jan1988. These are
calculated using the following formula:
time = (((((yyyy - 1988) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi) * 60 + ss;
/* Formatted on 2017/3/9 17:19:44 (QP5 v5.269.14213.34769) */
SELECT stamp, js, stamp - js
FROM (SELECT stamp
,(((((yyyy - 1988) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi) * 60 + ss js
FROM (SELECT stamp
,sequence#
,first_time
,NEXT_TIME
,TO_CHAR (next_time, 'yyyy') yyyy
,TO_CHAR (next_time, 'mm') mm
,TO_CHAR (next_time, 'dd') dd
,TO_CHAR (next_time, 'hh24') hh
,TO_CHAR (next_time, 'mi') mi
,TO_CHAR (next_time, 'ss') ss
FROM v$archived_log
WHERE dest_id = 1 AND name IS NOT NULL));
--//这样stamp-js相差最大也是5,比较接近,这样以前我的http://blog.itpub.net/267265/viewspace-1979123/算法存在错误,应该都是
--//按照上面的公式计算的.感觉这样反过来算时间就变成取余数运算.有点像递归.
/* Formatted on 2017/3/9 17:32:23 (QP5 v5.269.14213.34769) */
SELECT stamp,to_date(yyyy||'/'||mm||'/'||dd||' '||hh||':'||mi||':'||ss,'yyyy-mm-dd hh24:mi:ss') begin_time ,next_time FROM (
SELECT stamp
,FLOOR (stamp / (86400*31*12))+1988 yyyy
,FLOOR (MOD (stamp / (86400*31),12))+1 mm
,FLOOR (MOD (stamp / 86400, 31))+1 dd
,FLOOR (MOD (stamp / 3600, 24)) hh
,FLOOR (MOD (stamp / 60, 60)) mi
,MOD (stamp, 60) ss
,sequence#
,NEXT_TIME
,name
,first_time
FROM v$archived_log
WHERE dest_id = 1 AND name IS NOT NULL
ORDER BY 1);
--//自己可以写一些sql语句计算stamp转换日期:
$ 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);
--//写成函数:
CREATE OR REPLACE FUNCTION stamp_conv_time (stamp NUMBER)
RETURN DATE
IS
BEGIN
RETURN TO_DATE
(
TO_CHAR (FLOOR (stamp / (86400 * 31 * 12)) + 1988)
|| '/'
|| TO_CHAR (FLOOR (MOD (stamp / (86400 * 31), 12)) + 1)
|| '/'
|| TO_CHAR (FLOOR (MOD (stamp / 86400, 31)) + 1)
|| ' '
|| TO_CHAR (FLOOR (MOD (stamp / 3600, 24)))
|| ':'
|| TO_CHAR (FLOOR (MOD (stamp / 60, 60)))
|| ':'
|| TO_CHAR (MOD (stamp, 60))
,'yyyy-mm-dd hh24:mi:ss'
);
END;
/
--//以前分析错了,看来oracle内部这些时间戳应该都是安装这个公式计算得来的.
time = (((((yyyy - 1988) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi) * 60 + ss;