-
set lineshow off
-
SELECT TABLESPACE_NAME,
-
TS_TYPE,
-
TRUNC(TS_CURR_TOTAL)AS CUR_SIZE_MB,
-
TRUNC(TS_CURR_TOTAL-USED) AS CURR_FREE_MB,
-
TRUNC(USED) AS USED_MB,
-
TRUNC(PCT_CURR_USED) AS CURR_USED_PCT,
-
DECODE(STATUS,
-
0, 'ONLINE',
-
'OFFLINE') AS STATUS,
-
TRUNC(TS_TOTAL) AS MAX_SIZE_MB,
-
TRUNC(TS_TOTAL-USED) AS FREE_MB,
-
TRUNC(PCT_USED) AS USED_PCT
-
FROM ( SELECT DF.NAME AS TABLESPACE_NAME,
-
'PERMANENT' AS TS_TYPE,
-
STATUS$ AS STATUS,
-
MAX_SIZE AS TS_TOTAL,
-
CURR_MAX_SIZE AS TS_CURR_TOTAL,
-
DECODE((MAX_SIZE - USED_SIZE_T),
-
NULL, 0,
-
MAX_SIZE - USED_SIZE_T) AS FREE,
-
DECODE((CURR_MAX_SIZE - USED_SIZE_T),
-
NULL, 0,
-
CURR_MAX_SIZE - USED_SIZE_T) AS CURR_FREE,
-
DECODE(USED_SIZE_T,
-
NULL, 0,
-
USED_SIZE_T) AS USED,
-
DECODE(USED_SIZE_T,
-
NULL, 0,
-
ROUND(CAST(USED_SIZE_T / CURR_MAX_SIZE AS DECIMAL) * 100)) AS PCT_CURR_USED,
-
DECODE(USED_SIZE_T,
-
NULL, 0,
-
ROUND(CAST(USED_SIZE_T / MAX_SIZE AS DECIMAL) * 100)) AS PCT_USED
-
FROM (SELECT A.NAME,
-
A.ID,
-
A.STATUS$,
-
SUM(CAST(B.TOTAL_SIZE - B.FREE_SIZE AS DECIMAL) * PAGE() / 1024 / 1024) AS USED_SIZE_P,
-
SUM(CASE AUTO_EXTEND WHEN 0 THEN CAST(B.TOTAL_SIZE AS DECIMAL) * PAGE() / 1024 / 1024 WHEN 1 THEN B.MAX_SIZE END) AS MAX_SIZE,
-
SUM(CAST(B.TOTAL_SIZE AS DECIMAL) * PAGE() / 1024 / 1024) AS CURR_MAX_SIZE
-
FROM V$DATAFILE B,
-
V$TABLESPACE A
-
WHERE B.GROUP_ID = A.ID AND A.NAME NOT IN ('TEMP',
-
'ROLL')
-
GROUP BY A.NAME,
-
A.ID,
-
A.STATUS$) DF
-
LEFT JOIN (SELECT TS_ID,
-
SUM(N_FULL_EXTENT + N_FREE_EXTENT + N_FRAG_EXTENT) * SF_GET_EXTENT_SIZE() * PAGE() / 1024 / 1024 AS USED_SIZE_T
-
FROM V$SEGMENT_INFOS
-
GROUP BY TS_ID) SEG
-
ON SEG.TS_ID = DF.ID
-
UNION ALL
-
SELECT TABLESPACE_NAME,
-
'UNDO' AS TS_TYPE,
-
STATUS,
-
TS_TOTAL,
-
TOTAL AS TS_CURR_TOTAL,
-
FREE,
-
FREE AS CURR_FREE,
-
(TOTAL - FREE) AS USED,
-
ROUND(CAST((TOTAL - FREE) AS DECIMAL) * 100 / TOTAL) PCT_CURR_USED,
-
ROUND(CAST((TOTAL - FREE) AS DECIMAL) * 100 / TS_TOTAL) PCT_USED
-
FROM (SELECT T.NAME TABLESPACE_NAME,
-
T.STATUS$ STATUS,
-
SUM(CAST(FREE_SIZE AS DECIMAL) * PAGE() / 1024 / 1024) AS FREE,
-
SUM(CAST(D.TOTAL_SIZE AS DECIMAL) * PAGE() / 1024 / 1024) AS TOTAL,
-
SUM(CASE AUTO_EXTEND WHEN 0 THEN CAST(D.TOTAL_SIZE AS DECIMAL) * PAGE() / 1024 / 1024 WHEN 1 THEN D.MAX_SIZE END) AS TS_TOTAL
-
FROM V$TABLESPACE T,
-
V$DATAFILE D
-
WHERE T.ID = D.GROUP_ID AND T.NAME IN ('ROLL')
-
GROUP BY T.NAME,
-
T.STATUS$)
-
UNION ALL
-
SELECT TABLESPACE_NAME,
-
'TEMPORARY' AS TS_TYPE,
-
STATUS,
-
TS_TOTAL,
-
TOTAL AS TS_CURR_TOTAL,
-
FREE,
-
FREE AS CURR_FREE,
-
(TOTAL - FREE) USED,
-
ROUND(CAST((TOTAL - FREE) AS DECIMAL) * 100 / TOTAL) PCT_CURR_USED,
-
ROUND(CAST((TOTAL - FREE) AS DECIMAL) * 100 / TS_TOTAL) PCT_USED
-
FROM (SELECT T.NAME TABLESPACE_NAME,
-
T.STATUS$ STATUS,
-
CAST(FREE_SIZE AS DECIMAL) * PAGE() / 1024 / 1024 AS FREE,
-
CAST(D.TOTAL_SIZE AS DECIMAL) * PAGE() / 1024 / 1024 AS TOTAL,
-
CASE (SELECT SYS_VALUE
-
FROM V$PARAMETER
-
WHERE NAME = 'TEMP_SPACE_LIMIT') WHEN 0 THEN 99999999 ELSE (SELECT SYS_VALUE
-
FROM V$PARAMETER
-
WHERE NAME = 'TEMP_SPACE_LIMIT') END AS TS_TOTAL
-
FROM V$TABLESPACE T,
-
V$DATAFILE D
-
WHERE T.ID = D.GROUP_ID AND T.NAME IN ('TEMP')) )
-
ORDER BY TS_TYPE,
- PCT_CURR_USED DESC;