[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