[20170309]关于v$archived_log.stamp字段

[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;

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

转载于:http://blog.itpub.net/267265/viewspace-2135044/