[20170310]oracle内部时间戳的转换.txt-阿里云开发者社区

开发者社区> 数据库> 正文

[20170310]oracle内部时间戳的转换.txt

简介: [20170310]oracle内部时间戳的转换.txt --//昨天验证v$archived_log.stamp时,链接如下http://blog.itpub.net/267265/viewspace-2135044/,才发现自己以前犯了严重错误.

[20170310]oracle内部时间戳的转换.txt

--//昨天验证v$archived_log.stamp时,链接如下http://blog.itpub.net/267265/viewspace-2135044/,才发现自己以前犯了严重错误.
--//想起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;

--//我重新验证一下以前写的blog,发现自己以前认识是错误的,特此更正.
--//链接:
http://blog.itpub.net/267265/viewspace-1979123/ => [20160119]V$RMAN_OUTPUT的stamp.txt
http://blog.itpub.net/267265/viewspace-2076659/ => [20160407]bbed修改文件头2(补充).txt

--//自己也写了转换程序

--//stamp convert date
$ 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);

--// date convert stamp:
$ cat convstamp.sql
SELECT  '&&1' time,(((((yyyy - 1988) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi)* 60 + ss  stamp
  FROM (SELECT TO_CHAR (TO_DATE ('&&1', 'yyyy-mm-dd hh24:mi:ss'), 'yyyy')
                  yyyy
              ,TO_CHAR (TO_DATE ('&&1', 'yyyy-mm-dd hh24:mi:ss'), 'mm') mm
              ,TO_CHAR (TO_DATE ('&&1', 'yyyy-mm-dd hh24:mi:ss'), 'dd') dd
              ,TO_CHAR (TO_DATE ('&&1', 'yyyy-mm-dd hh24:mi:ss'), 'hh24') hh
              ,TO_CHAR (TO_DATE ('&&1', 'yyyy-mm-dd hh24:mi:ss'), 'mi') mi
              ,TO_CHAR (TO_DATE ('&&1', 'yyyy-mm-dd hh24:mi:ss'), 'ss') 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;
/

--//现在才明白redo dump时:
DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redo03.log'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
VALIDATE ONLY
FILE HEADER:
    Compatibility Vsn = 186647552=0xb200400
    Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
    Activation ID=1337448558=0x4fb7d86e
    Control Seq=36185=0x8d59, File size=102400=0x19000
    File Number=3, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000697, SCN 0x0003175de792-0xffffffffffff"
thread: 1 nab: 0x42f seq: 0x000002b9 hws: 0x2 eot: 1 dis: 0
resetlogs count: 0x35711eb0 scn: 0x0000.000e2006 (925702)
prev resetlogs count: 0x3121c97a scn: 0x0000.00000001 (1)
Low  scn: 0x0003.175de792 (13276931986) 03/09/2017 10:02:36
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.000e2006 (925702) 11/24/2015 09:11:12
Thread closed scn: 0x0003.175deb08 (13276932872) 03/09/2017 10:09:16
Disk cksum: 0xb2bf Calc cksum: 0xb2bf
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery  01/01/1988 00:00:00
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//这里就是oracle内部的时间起点.
Most recent redo scn: 0x0000.00000000
Largest LWN: 20 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x800000
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
Zero blocks: 8
Format ID is 2
redo log key is 5843a3a529428c3678c4ec42b36548f
redo log key flag is 5
Enabled redo threads: 1
END OF REDO DUMP

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章