SELECT A.SEGMENT_OWNER,
A.SEGMENT_NAME,
A.TABLE_SIZE,
A.LOB_SIZE,
A.INDEX_SIZE,
A.TOTAL_SIZE,
TRIM(
CASE WHEN (A.TOTAL_SIZE / ( 1024 * 1024 * 1024) ) >= 1 THEN
TO_CHAR ( (A.TOTAL_SIZE / ( 1024 * 1024 * 1024)), '999,999,999.99') || ' GB'
WHEN (A.TOTAL_SIZE / ( 1024 * 1024)) >= 1 THEN
TO_CHAR ( (A.TOTAL_SIZE / ( 1024 * 1024)), '999,999,999.99') || ' MB'
ELSE TO_CHAR (A.TOTAL_SIZE, '999,999,999.99') || ' KB'
END
) AS NOTE,
TO_CHAR (SYSDATE, 'YYYYMMDD') AS MODIFIED_DATE,
0 AS DATA_COUNT
FROM ( WITH TAB
AS ( SELECT UCC.OWNER AS SEGMENT_OWNER,
UCC.TABLE_NAME AS SEGMENT_NAME
FROM DBA_COL_COMMENTS UCC, DBA_TABLES UT -- ALL_TAB_COLUMNS
WHERE UCC.COLUMN_NAME = 'AID'
AND UCC.OWNER IN ('AA',BB',CC')
AND UCC.OWNER = UT.OWNER
AND UCC.TABLE_NAME = UT.TABLE_NAME
)
SELECT A.SEGMENT_OWNER,
A.SEGMENT_NAME,
SUM (A.TABLE_SIZE) AS TABLE_SIZE,
SUM (A.LOB_SIZE) AS LOB_SIZE,
SUM (A.INDEX_SIZE) AS INDEX_SIZE,
SUM (A.TABLE_SIZE + A.LOB_SIZE + A.INDEX_SIZE) AS TOTAL_SIZE
FROM ( -- Tables
SELECT TAB.SEGMENT_OWNER,
TAB.SEGMENT_NAME,
NVL (SUM (S.BYTES), 0) AS TABLE_SIZE,
0 AS LOB_SIZE,
0 AS INDEX_SIZE
FROM TAB, DBA_SEGMENTS S
WHERE S.OWNER = TAB.SEGMENT_OWNER
AND S.SEGMENT_NAME = TAB.SEGMENT_NAME
AND S.SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
GROUP BY TAB.SEGMENT_OWNER, TAB.SEGMENT_NAME
UNION ALL
-- LOB Segments
SELECT TAB.SEGMENT_OWNER,
TAB.SEGMENT_NAME,
0 AS TABLE_SIZE,
NVL (SUM (S.BYTES), 0) AS LOB_SIZE,
0 AS INDEX_SIZE
FROM TAB, DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = L.OWNER
AND S.SEGMENT_NAME = L.SEGMENT_NAME
AND S.SEGMENT_TYPE IN ('LOBSEGMENT','LOB PARTITION','LOB SUBPARTITION')
AND L.TABLE_NAME = TAB.SEGMENT_NAME
AND L.OWNER = TAB.SEGMENT_OWNER
GROUP BY TAB.SEGMENT_OWNER, TAB.SEGMENT_NAME
UNION ALL
-- LOB Indexes
SELECT TAB.SEGMENT_OWNER,
TAB.SEGMENT_NAME,
0 AS TABLE_SIZE,
NVL (SUM (S.BYTES), 0) AS LOB_SIZE,
0 AS INDEX_SIZE
FROM TAB, DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = L.OWNER
AND S.SEGMENT_NAME = L.SEGMENT_NAME
AND S.SEGMENT_TYPE = 'LOBINDEX'
AND L.TABLE_NAME = TAB.SEGMENT_NAME
AND L.OWNER = TAB.SEGMENT_OWNER
GROUP BY TAB.SEGMENT_OWNER, TAB.SEGMENT_NAME
UNION ALL
-- Indexes
SELECT TAB.SEGMENT_OWNER,
TAB.SEGMENT_NAME,
0 AS TABLE_SIZE,
0 AS LOB_SIZE,
NVL (SUM (S.BYTES), 0) AS INDEX_SIZE
FROM TAB, DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = I.OWNER
AND S.SEGMENT_NAME = I.INDEX_NAME
AND S.SEGMENT_TYPE IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
AND I.TABLE_NAME = TAB.SEGMENT_NAME
AND I.TABLE_OWNER = TAB.SEGMENT_OWNER
GROUP BY TAB.SEGMENT_OWNER, TAB.SEGMENT_NAME
) A
GROUP BY A.SEGMENT_OWNER, A.SEGMENT_NAME
) A