oracle 如何查询Headroom
1、打开sqlplus或者plsql
1、SELECT tim,
gscn,
round(rate),
round((chk16kscn - gscn) / 24 / 3600 / 16 / 1024, 1) "Headroom"
FROM (select tim,
gscn,
rate,
((((to_number(to_char(tim, 'YYYY')) - 1988) * 12 * 31 * 24 * 60 * 60) +
((to_number(to_char(tim, 'MM')) - 1) * 31 * 24 * 60 * 60) +
(((to_number(to_char(tim, 'DD')) - 1)) * 24 * 60 * 60) +
(to_number(to_char(tim, 'HH24')) * 60 * 60) +
(to_number(to_char(tim, 'MI')) * 60) +
(to_number(to_char(tim, 'SS')))) * (16 * 1024)) chk16kscn
from (select FIRST_TIME tim,
FIRST_CHANGE# gscn,
((NEXT_CHANGE# - FIRST_CHANGE#) /
((NEXT_TIME - FIRST_TIME) * 24 * 60 * 60)) rate
from v$archived_log
where (next_time > first_time)))
order by 1, 2;
WITH sysstat AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sysstat ss, dba_hist_snapshot sn
where ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = 'calls to kcmgas')
select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char(END_INTERVAL_TIME, '_hh24_mi') date_time,
stat_name,
round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
+ extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60
+ extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec
from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0;