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

[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,发现自己以前认识是错误的,特此更正.

--//链接:

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

--//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