[20170315]数据类型interval day to second.txt
--上午监测dg应用情况时,查询如下视图:
1.前提:
$ cat dg_status.sql
column name format a30;
column value format a30
column unit format a30
column time_computed format a30
select name,value,unit,to_char(to_date(time_computed,'mm/dd/yyyy hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') time_computed from v$dataguard_stats;
SYS@bookdg> @ &r/dg/dg_status.sql
NAME VALUE UNIT TIME_COMPUTED
------------------------------ ------------------------------ ------------------------------ ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval 2017-03-15 14:45:44
apply lag +00 02:40:13 day(2) to second(0) interval 2017-03-15 14:45:44
apply finish time +00 00:00:04.201 day(2) to second(3) interval 2017-03-15 14:45:44
estimated startup time 7 second 2017-03-15 14:45:44
--//name='apply lag',values=+00 02:40:13,单位unit= day(2) to second(0) interval.
--//才知道oracle还有一种数据类型是interval day to second . 里面的数字表示精度.简单探究一下,我主要的目的是转换成秒.
2.测试:
SCOTT@book> @ &r/ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t ( a interval day to second);
Table created.
SCOTT@book> @ &r/desc t
Name Null? Type
------------------------------- -------- ----------------------------
1 A INTERVAL DAY(2) TO SECOND(6)
--//缺省不指定 天数精度是2,秒6.
SCOTT@book> insert into t values (TO_DSINTERVAL('+00 02:40:13')) ;
1 row created.
SCOTT@book> insert into t values (INTERVAL '4 5:12:10.222' DAY TO SECOND(3) ) ;
1 row created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select * from t;
A
--------------------
+00 02:40:13.000000
+04 05:12:10.222000
--//简单探究转储:
SCOTT@book> select a c20,dump(a,16) c50 ,dump(a,10) c50 from t;
C20 C50 C50
-------------------- -------------------------------------------------- --------------------------------------------------
+00 02:40:13.000000 Typ=183 Len=11: 80,0,0,0,3e,64,49,80,0,0,0 Typ=183 Len=11: 128,0,0,0,62,100,73,128,0,0,0
+04 05:12:10.222000 Typ=183 Len=11: 80,0,0,4,41,48,46,8d,3b,73,80 Typ=183 Len=11: 128,0,0,4,65,72,70,141,59,115,128
--//猜测一下0x80估计表示+, 中间3位表示天数,后面的时分秒 再减去60就对上.
--//小数点表示的nanoseconds,需要长度11.
SCOTT@book> @ &r/10to16 222000000
10 to 16 HEX REVERSE16
-------------- ------------------
000000d3b7380 0x80733b0d
--//只不过前面的0d变成8d.
3.如何转换成秒呢?
--参考http://yangtingkun.net/?p=896的链接:
SELECT EXTRACT(DAY FROM a) * 86400
+ EXTRACT(HOUR FROM a) * 3600
+ EXTRACT(MINUTE FROM a) * 60
+ EXTRACT(SECOND FROM a) INTERVAL
FROM t;
INTERVAL
----------
9613
364330.222
--//感觉有点麻烦!简单一点写成这样.
SCOTT@book> select (sysdate-(sysdate- a))*86400 ss from t;
SS
----------
9613
364331
--//注一定要这样写,如果写成这样.
SCOTT@book> select (sysdate-sysdate + a))*86400 ss from t;
select (sysdate-sysdate + a))*86400 ss from t
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
--//另外还有函数TO_DSINTERVAL转换成这种类型.
SYS@bookdg> select (sysdate-(sysdate- TO_DSINTERVAL(value)))*86400 ss,v$dataguard_stats.* from v$dataguard_stats where name ='apply lag';
SS NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
12186 apply lag +00 03:23:06 day(2) to second(0) interval 03/15/2017 15:28:36 03/15/2017 15:28:35
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2135404/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2135404/