本文共 3436 字，大约阅读时间需要 11 分钟。
[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,发现自己以前认识是错误的,特此更正.
--//链接: => [20160119]V$RMAN_OUTPUT的stamp.txt => [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转载地址：http://bsybx.baihongyu.com/