Oracle 壓縮表與壓縮表空間查詢腳本實用指南

前言

資料壓縮可大幅降低儲存成本並提升掃描效能。本文提供兩支實用腳本，快速辨識資料庫中哪些表、哪些表空間已啟用壓縮。

1. 查詢所有壓縮表

透過底層 SYS.TAB$ 的 SPARE1 位元旗標 (bit 17 = 131072) 判斷：

SELECT u.name AS owner,
       o.name AS table_name
FROM   sys.tab$ t,
       sys.obj$ o,
       sys.user$ u
WHERE  BITAND(t.spare1, 131072) = 131072
AND    o.obj# = t.obj#
AND    o.owner# = u.user#;

說明：需 SELECT ANY DICTIONARY 權限。結果列出擁有者與表名，可進一步 JOIN DBA_TABLES 取得壓縮類型 (COMPRESS_FOR : BASIC/OLTP/QUERY LOW/ARCHIVE HIGH 等)。

2. 查詢壓縮表空間

表空間屬性儲於 SYS.TS$ 的 FLAGS 位元 (bit 6 = 64)：

SELECT name
FROM   sys.ts$
WHERE  BITAND(flags, 64) = 64;

回傳名單即為預設啟用壓縮的表空間（建表時若未指定 NOCOMPRESS 將自動壓縮）。

3. 進階：搭配 DBA 視圖取得完整資訊

-- 表級詳細資訊
SELECT owner, table_name, compression, compress_for
FROM   dba_tables
WHERE  compression = 'ENABLED';

-- 表空間預設壓縮屬性
SELECT tablespace_name, def_tab_compression, compress_for
FROM   dba_tablespaces
WHERE  def_tab_compression = 'ENABLED';

4. 實務建議

• 定期排程執行上述腳本，納入資產管理報表。
• 壓縮表空間建立時指定 DEFAULT COMPRESS FOR OLTP (11gR2+) 或 DEFAULT COMPRESS FOR QUERY HIGH (12c+)，新表自動受惠。
• 注意 HCC (Hybrid Columnar Compression) 僅支援 Exadata/Engineered Systems 或特定儲存陣列。