Oracle Database 确认表空间大小的方法

sql:

WITH object_size AS (
    SELECT segment_name,
           owner,
           segment_type,
           tablespace_name,
           SUM(bytes) total_bytes
    FROM dba_segments
    GROUP BY segment_name, owner, segment_type, tablespace_name
),
table_size AS (
    SELECT segment_name AS table_name,
           owner,
           tablespace_name,
           total_bytes AS table_bytes
    FROM object_size
    WHERE segment_type = 'TABLE'
),
index_size AS (
    SELECT i.table_name,
           i.owner,
           SUM(s.total_bytes) AS index_bytes
    FROM dba_indexes i
    JOIN object_size s ON i.index_name = s.segment_name AND i.owner = s.owner
    WHERE s.segment_type = 'INDEX'
    GROUP BY i.table_name, i.owner
),
lob_size AS (
    SELECT l.table_name,
           l.owner,
           SUM(s.total_bytes) AS lob_bytes
    FROM dba_lobs l
    JOIN object_size s ON l.segment_name = s.segment_name AND l.owner = s.owner
    WHERE s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
    GROUP BY l.table_name, l.owner
),
total_size AS (
    SELECT t.table_name,
           t.owner,
           t.tablespace_name,
           t.table_bytes,
           COALESCE(i.index_bytes, 0) AS index_bytes,
           COALESCE(l.lob_bytes, 0) AS lob_bytes,
           (t.table_bytes + COALESCE(i.index_bytes, 0) + COALESCE(l.lob_bytes, 0)) AS total_bytes
    FROM table_size t
    LEFT JOIN index_size i ON t.table_name = i.table_name AND t.owner = i.owner
    LEFT JOIN lob_size l ON t.table_name = l.table_name AND t.owner = l.owner
)
SELECT table_name,
       owner,
       tablespace_name,
       ROUND(table_bytes / 1024 / 1024, 2) AS table_size_mb,
       ROUND(index_bytes / 1024 / 1024, 2) AS index_size_mb,
       ROUND(lob_bytes / 1024 / 1024, 2) AS lob_size_mb,
       ROUND(total_bytes / 1024 / 1024, 2) AS total_size_mb
FROM total_size
WHERE table_name in ('DOC_CHUNKS', 'DOCUMENTATION_TAB')
ORDER BY total_bytes DESC;