Oracle 查詢表空間物件完整 SQL：DBA_SEGMENTS、DBA_TABLES 與 DBA_LOBS 多維視角

方法一：DBA_SEGMENTS 統計視角（推薦）

查詢表空間內所有段（表、索引、LOB 等）及其大小：

SELECT tablespace_name      AS TABLESPACE_NAME,
       segment_name         AS SEGMENT_NAME,
       SUM(bytes)/1024/1024 AS SEGMENT_SIZE_MB
FROM dba_segments
WHERE tablespace_name = '&TABLESPACE_NAME'
GROUP BY tablespace_name, segment_name
ORDER BY 3;

方法二：DBA_SEGMENTS 詳細資訊視角

包含擁有者、段類型（TABLE/INDEX/LOBSEGMENT 等）：

SELECT owner                AS OWNER,
       segment_name         AS SEGMENT_NAME,
       segment_type         AS SEGMENT_TYPE,
       SUM(bytes)/1024/1024 AS SEGMENT_SIZE_MB
FROM dba_segments
WHERE tablespace_name = '&TABLESPACE_NAME'
GROUP BY owner, segment_name, segment_type
ORDER BY 4;

方法三：分類別查詢視角（TABLE/INDEX/LOB）

針對特定物件類型，結合 DBA_TABLES、DBA_INDEXES、DBA_LOBS：

SELECT owner, 'TABLE' AS SEGMENT_TYPE, table_name AS SEGMENT_NAME
FROM dba_tables
WHERE tablespace_name = '&TABLESPACE_NAME'
UNION ALL
SELECT owner, 'INDEX' AS SEGMENT_TYPE, index_name AS SEGMENT_NAME
FROM dba_indexes
WHERE tablespace_name = '&TABLESPACE_NAME'
UNION ALL
SELECT owner, 'LOBSEGMENT' AS SEGMENT_TYPE, segment_name AS SEGMENT_NAME
FROM dba_lobs
WHERE tablespace_name = '&TABLESPACE_NAME';

三種方法比較與選擇建議

進階應用：找出表空間前 10 大物件

SELECT * FROM (
  SELECT owner, segment_name, segment_type,
         ROUND(SUM(bytes)/1024/1024,2) AS SIZE_MB
  FROM dba_segments
  WHERE tablespace_name = '&TABLESPACE_NAME'
  GROUP BY owner, segment_name, segment_type
  ORDER BY 4 DESC
) WHERE ROWNUM <= 10;