103 lines
3.4 KiB
MySQL
103 lines
3.4 KiB
MySQL
set linesize 150
|
|
set pagesize 9999
|
|
set echo off
|
|
set verify off
|
|
|
|
column undo_retention_time format a20
|
|
|
|
select
|
|
inst_id,
|
|
value undo_retention_time
|
|
from
|
|
gv$parameter
|
|
where
|
|
name = 'undo_retention'
|
|
order by
|
|
inst_id
|
|
;
|
|
|
|
|
|
column undo_space format 999G999G999D99
|
|
column max_undo_space format 999G999G999D99
|
|
column undo_tbs format a15
|
|
column q_length format a12
|
|
column tu_length format a12
|
|
|
|
select
|
|
tablespace_name,
|
|
sum(bytes)/1024/1024 undo_space,
|
|
sum(maxbytes)/1024/1024 max_undo_space,
|
|
max(autoextensible)
|
|
from
|
|
dba_data_files
|
|
where
|
|
tablespace_name in (select value from gv$parameter where name = 'undo_tablespace')
|
|
group by
|
|
tablespace_name
|
|
order by
|
|
tablespace_name
|
|
;
|
|
|
|
column begin_time format a17
|
|
column end_time format a17
|
|
column retention_undo_usage format 9G999G999D99 heading "Undo ret period"
|
|
column curr_undo_usage format 9G999D99 heading "undo usage"
|
|
column pct_used format 999D99
|
|
column ts_ss format 99D99
|
|
column err format a12
|
|
column maxqueryid format a15
|
|
column inst_id format 999
|
|
|
|
prompt err: unxpblkreucnt / ssolderrcnt / nospaceerrcnt
|
|
|
|
break on inst_id skip 1
|
|
|
|
select
|
|
inst_id,
|
|
to_char(begin_time,'DD/MM/YYYY HH24:MI') begin_time,
|
|
to_char(end_time,'DD/MM/YYYY HH24:MI') end_time,
|
|
( select
|
|
name
|
|
from
|
|
v$tablespace
|
|
where
|
|
ts# = undotsn
|
|
) undo_tbs,
|
|
curr_undo_usage,
|
|
retention_undo_usage,
|
|
(100 * retention_undo_usage / (select sum(greatest(bytes,maxbytes))/1024/1024 from dba_data_files where tablespace_name = (select name from v$tablespace where ts# = undotsn))) pct_used,
|
|
ts_dd || ' ' || ts_hh || ':' || ts_mi || ':' || ts_ss q_length,
|
|
tund_dd || ' ' || tund_hh || ':' || tund_mi || ':' || tund_ss tu_length,
|
|
maxqueryid,
|
|
err
|
|
from
|
|
( select
|
|
inst_id,
|
|
undotsn,
|
|
begin_time,
|
|
end_time,
|
|
undoblks * (select block_size from dba_tablespaces where tablespace_name = (select name from v$tablespace where ts# = undotsn)) /1024/1024 curr_undo_usage,
|
|
((activeblks + unexpiredblks) * (select block_size from dba_tablespaces where tablespace_name = (select name from v$tablespace where ts# = undotsn)) /1024/1024) retention_undo_usage,
|
|
decode(maxquerylen, 0, 0, extract(day from (systimestamp + numtodsinterval(maxquerylen,'second') - systimestamp))) ts_dd,
|
|
decode(maxquerylen, 0, 0, extract(hour from (systimestamp + numtodsinterval(maxquerylen,'second') - systimestamp))) ts_hh,
|
|
decode(maxquerylen, 0, 0, extract(minute from (systimestamp + numtodsinterval(maxquerylen,'second') - systimestamp))) ts_mi,
|
|
decode(maxquerylen, 0, 0, floor(extract(second from (systimestamp + numtodsinterval(maxquerylen,'second') - systimestamp)))) ts_ss,
|
|
decode(tuned_undoretention, 0, 0, extract(day from (systimestamp + numtodsinterval(tuned_undoretention,'second') - systimestamp))) tund_dd,
|
|
decode(tuned_undoretention, 0, 0, extract(hour from (systimestamp + numtodsinterval(tuned_undoretention,'second') - systimestamp))) tund_hh,
|
|
decode(tuned_undoretention, 0, 0, extract(minute from (systimestamp + numtodsinterval(tuned_undoretention,'second') - systimestamp))) tund_mi,
|
|
decode(tuned_undoretention, 0, 0, floor(extract(second from (systimestamp + numtodsinterval(tuned_undoretention,'second') - systimestamp)))) tund_ss,
|
|
maxqueryid,
|
|
unxpblkreucnt || '/' || ssolderrcnt || '/' || nospaceerrcnt err
|
|
from
|
|
gv$undostat
|
|
) U
|
|
where
|
|
begin_time >= sysdate -1
|
|
and end_time <= sysdate
|
|
order by
|
|
inst_id,
|
|
U.begin_time
|
|
;
|
|
|
|
clear breaks
|