Oracle 分區表資訊查詢完全手冊：DBA/ALL/USER 視圖對照與實戰 SQL

分區表是大型資料庫管理海量資料的核心機制。本文系統整理 Oracle 提供的分區相關資料字典視圖，並提供可直接執行的查詢腳本，涵蓋分區表識別、分區類型、分區詳情、分區鍵欄位、子分區等完整資訊。

核心視圖三層架構

快速判斷某表是否為分區表

SELECT partitioned
FROM all_tables
WHERE owner = 'SCOTT' AND table_name = 'SALES';
-- 回傳 YES / NO

誤區：TABLESPACE_NAME IS NULL 不等於分區表（普通表也可能無表空間）。

實戰查詢腳本集

1. 列出所有分區表及其類型、分區數

SELECT owner, table_name, partitioning_type, subpartitioning_type, partition_count
FROM dba_part_tables
ORDER BY owner, table_name;

2. 查看指定表的每個分區詳細資訊

SELECT table_name, partition_name, tablespace_name, high_value, num_rows, blocks, compression
FROM dba_tab_partitions
WHERE table_name = 'GPRS_CELLTOPVOL_WK'
ORDER BY partition_position;

HIGH_VALUE 為分區上界值（LONG 型別，需用 DBMS_METADATA 或 PL/SQL 讀取完整）。

3. 查看分區鍵欄位

SELECT a.name, a.object_type, b.column_name, b.column_position
FROM dba_part_tables a
JOIN dba_part_key_columns b ON a.owner = b.owner AND a.name = b.name
WHERE a.name = 'GPRS_CELLTOPVOL_WK';

4. 組合分區：查看子分區

SELECT table_name, partition_name, subpartition_name, tablespace_name, high_value
FROM dba_tab_subpartitions
WHERE table_name = 'SALES_COMPOSITE'
ORDER BY partition_name, subpartition_position;

5. 子分區鍵欄位

SELECT name, column_name, column_position
FROM dba_subpart_key_columns
WHERE name = 'SALES_COMPOSITE';

建立測試分區表（Range + Hash 組合）

CREATE TABLE sales_composite (
  sale_id NUMBER,
  sale_date DATE,
  region_id NUMBER,
  amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (region_id)
SUBPARTITIONS 4
(
  PARTITION p_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01','YYYY-MM-DD')),
  PARTITION p_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01','YYYY-MM-DD')),
  PARTITION p_2023_q3 VALUES LESS THAN (TO_DATE('2023-10-01','YYYY-MM-DD')),
  PARTITION p_2023_q4 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD'))
);

維護常用操作

• 新增分區：ALTER TABLE sales ADD PARTITION p_2024_q1 VALUES LESS THAN (DATE '2024-04-01');
• 合併分區：ALTER TABLE sales MERGE PARTITIONS p_2023_q1, p_2023_q2 INTO PARTITION p_2023_h1;
• 分割分區：ALTER TABLE sales SPLIT PARTITION p_2023_q3 AT (DATE '2023-08-15') INTO (PARTITION p_2023_q3a, PARTITION p_2023_q3b);
• 交換分區：ALTER TABLE sales EXCHANGE PARTITION p_2023_q1 WITH TABLE staging_sales_q1 INCLUDING INDEXES WITHOUT VALIDATION;
• 截斷分區：ALTER TABLE sales TRUNCATE PARTITION p_2023_q1 DROP STORAGE;

效能提示

• 查詢 DBA_TAB_PARTITIONS 時 HIGH_VALUE 為 LONG，大量表會拖慢查詢；建議僅選擇必要欄位或用 DBMS_METADATA.GET_DDL 取得完整定義。
• 分區裁剪只在 WHERE 條件包含分區鍵且使用等值/範圍/IN 列表時生效。
• 定期收集分區級統計資訊：DBMS_STATS.GATHER_TABLE_STATS(ownname, tabname, granularity => 'PARTITION');