统计oracle库有多大,求统计数据库中每个表的大小（包括索引）的SQL！

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