[20170310]oracle內部時間戳的轉換.txt

[20170310]oracle內部時間戳的轉換.txt

--//昨天驗證v$archived_log.stamp時,連結如下http://blog.itpub.net/267265/viewspace-2135044/,才發現自己以前犯了嚴重錯誤.
--//想起轉儲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