--UNDO使用率
select AA.TABLESPACE_NAME,
ROUND((nvl(BB.UNEXPIRED_USED_GB, 0) + nvl(BB.ACTIVE_USED_GB, 0)) /
AA.TOTAL_GB * 100,
2) USED_PCT,
ROUND(nvl(BB.ACTIVE_USED_GB, 0) / AA.TOTAL_GB * 100, 2) PCT_ACTIVE,
ROUND(nvl(BB.UNEXPIRED_USED_GB, 0) / AA.TOTAL_GB * 100, 2) PCT_UNEXPIRED,
ROUND(nvl(BB.EXPIRED_USED_GB, 0) / AA.TOTAL_GB * 100, 2) PCT_EXPIRED,
maxquerylen,
tuned_undoretention,
pct_retention,
AA.TOTAL_GB,
nvl(BB.ACTIVE_USED_GB, 0) ACTIVE_USED_GB,
nvl(BB.UNEXPIRED_USED_GB, 0) UNEXPIRED_USED_GB,
nvl(BB.EXPIRED_USED_GB, 0) EXPIRED_USED_GB,
nvl(BB.ACTIVE_EXTENT_CNT, 0) ACTIVE_EXTENT_CNT,
nvl(BB.UNEXPIRED_EXTENT_CNT, 0) UNEXPIRED_EXTENT_CNT,
nvl(BB.EXPIRED_EXTENT_CNT, 0) EXPIRED_EXTENT_CNT
from (select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 TOTAL_GB
from DBA_DATA_FILES
where tablespace_name IN ('UNDOTBS2', 'UNDOTBS1')
GROUP BY tablespace_name) AA,
(select lix.TABLESPACE_NAME,
max(case
when lix.STATUS = 'ACTIVE' then
USED_GB
end) ACTIVE_USED_GB,
max(case
when lix.STATUS = 'UNEXPIRED' then
USED_GB
end) UNEXPIRED_USED_GB,
max(case
when lix.STATUS = 'EXPIRED' then
USED_GB
end) EXPIRED_USED_GB,
max(case
when lix.STATUS = 'ACTIVE' then
EXTENT_CNT
end) ACTIVE_EXTENT_CNT,
max(case
when lix.STATUS = 'UNEXPIRED' then
EXTENT_CNT
end) UNEXPIRED_EXTENT_CNT,
max(case
when lix.STATUS = 'EXPIRED' then
EXTENT_CNT
end) EXPIRED_EXTENT_CNT
from (SELECT TABLESPACE_NAME,
STATUS,
SUM(BYTES) / 1024 / 1024 / 1024 USED_GB,
COUNT(*) EXTENT_CNT
FROM DBA_UNDO_EXTENTS
GROUP BY TABLESPACE_NAME, STATUS) lix
group by lix.TABLESPACE_NAME) BB,
(select max(maxquerylen) maxquerylen,
max(tuned_undoretention) tuned_undoretention,
round(max(maxquerylen) / max(tuned_undoretention) * 100, 2) pct_retention
from DBA_HIST_UNDOSTAT) CC
WHERE AA.TABLESPACE_NAME = BB.TABLESPACE_NAME
ORDER BY 1;
select AA.TABLESPACE_NAME,
ROUND((nvl(BB.UNEXPIRED_USED_GB, 0) + nvl(BB.ACTIVE_USED_GB, 0)) /
AA.TOTAL_GB * 100,
2) USED_PCT,
ROUND(nvl(BB.ACTIVE_USED_GB, 0) / AA.TOTAL_GB * 100, 2) PCT_ACTIVE,
ROUND(nvl(BB.UNEXPIRED_USED_GB, 0) / AA.TOTAL_GB * 100, 2) PCT_UNEXPIRED,
ROUND(nvl(BB.EXPIRED_USED_GB, 0) / AA.TOTAL_GB * 100, 2) PCT_EXPIRED,
maxquerylen,
tuned_undoretention,
pct_retention,
AA.TOTAL_GB,
nvl(BB.ACTIVE_USED_GB, 0) ACTIVE_USED_GB,
nvl(BB.UNEXPIRED_USED_GB, 0) UNEXPIRED_USED_GB,
nvl(BB.EXPIRED_USED_GB, 0) EXPIRED_USED_GB,
nvl(BB.ACTIVE_EXTENT_CNT, 0) ACTIVE_EXTENT_CNT,
nvl(BB.UNEXPIRED_EXTENT_CNT, 0) UNEXPIRED_EXTENT_CNT,
nvl(BB.EXPIRED_EXTENT_CNT, 0) EXPIRED_EXTENT_CNT
from (select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 TOTAL_GB
from DBA_DATA_FILES
where tablespace_name IN ('UNDOTBS2', 'UNDOTBS1')
GROUP BY tablespace_name) AA,
(select lix.TABLESPACE_NAME,
max(case
when lix.STATUS = 'ACTIVE' then
USED_GB
end) ACTIVE_USED_GB,
max(case
when lix.STATUS = 'UNEXPIRED' then
USED_GB
end) UNEXPIRED_USED_GB,
max(case
when lix.STATUS = 'EXPIRED' then
USED_GB
end) EXPIRED_USED_GB,
max(case
when lix.STATUS = 'ACTIVE' then
EXTENT_CNT
end) ACTIVE_EXTENT_CNT,
max(case
when lix.STATUS = 'UNEXPIRED' then
EXTENT_CNT
end) UNEXPIRED_EXTENT_CNT,
max(case
when lix.STATUS = 'EXPIRED' then
EXTENT_CNT
end) EXPIRED_EXTENT_CNT
from (SELECT TABLESPACE_NAME,
STATUS,
SUM(BYTES) / 1024 / 1024 / 1024 USED_GB,
COUNT(*) EXTENT_CNT
FROM DBA_UNDO_EXTENTS
GROUP BY TABLESPACE_NAME, STATUS) lix
group by lix.TABLESPACE_NAME) BB,
(select max(maxquerylen) maxquerylen,
max(tuned_undoretention) tuned_undoretention,
round(max(maxquerylen) / max(tuned_undoretention) * 100, 2) pct_retention
from DBA_HIST_UNDOSTAT) CC
WHERE AA.TABLESPACE_NAME = BB.TABLESPACE_NAME
ORDER BY 1;