Oracle 變更表擁有者實戰：利用分區交換實現跨 Schema 移動表

問題背景

Oracle 無直接 ALTER TABLE ... OWNER TO new_user 指令。傳統做法是 CREATE TABLE new_user.t AS SELECT * FROM old_user.t，但大表會產生大量 Redo、鎖定久、需重建索引/權限/約束。

分區交換法：秒級完成、零 Redo、保留索引

利用 ALTER TABLE ... EXCHANGE PARTITION 特性：分區段與非分區表可瞬間交換段 Header，不移動實際資料塊。

前置條件

• 目標 Schema 需有 CREATE TABLE、CREATE ANY TABLE 權限
• 源表無外鍵參考（或暫時停用）
• 源表為非分區表

操作步驟

-- 1. 在目標 Schema 建立分區表（單一分區涵蓋所有值）
CONN target_user/target_pwd
CREATE TABLE temp_exch (
  id   NUMBER,
  name VARCHAR2(20)
) PARTITION BY RANGE (id) (
  PARTITION p_all VALUES LESS THAN (MAXVALUE)
);

-- 2. 建立臨時非分區表（結構需與源表完全一致）
CREATE TABLE t1 (id NUMBER, name VARCHAR2(20));

-- 3. 交換：源表段 → 目標 Schema 分區表的分區
ALTER TABLE temp_exch EXCHANGE PARTITION p_all 
WITH TABLE source_user.t1 
INCLUDING INDEXES 
WITHOUT VALIDATION;

-- 4. 交換回非分區表（現在屬於目標 Schema）
ALTER TABLE temp_exch EXCHANGE PARTITION p_all 
WITH TABLE t1 
INCLUDING INDEXES 
WITHOUT VALIDATION;

-- 5. 清理臨時物件
DROP TABLE temp_exch PURGE;
DROP TABLE t1 PURGE;

關鍵參數說明

• INCLUDING INDEXES：同時交換索引段，避免重建
• WITHOUT VALIDATION：跳過資料檢查（需確保資料符合分區鍵範圍），大幅加速
• PARTITION BY RANGE (id) VALUES LESS THAN (MAXVALUE)：單分區涵蓋所有可能值

-- 準備源資料
CONN test/test
CREATE TABLE t1 (id NUMBER, name VARCHAR2(20));
INSERT INTO t1 VALUES (1, 'xx');
COMMIT;
GRANT ALL ON t1 TO test1;

-- 目標 Schema 執行交換
CONN test1/test1
CREATE TABLE temp_exch (id NUMBER, name VARCHAR2(20))
PARTITION BY RANGE (id) (PARTITION p_all VALUES LESS THAN (MAXVALUE));
CREATE TABLE t1 (id NUMBER, name VARCHAR2(20));

ALTER TABLE temp_exch EXCHANGE PARTITION p_all 
WITH TABLE test.t1 INCLUDING INDEXES WITHOUT VALIDATION;

ALTER TABLE temp_exch EXCHANGE PARTITION p_all 
WITH TABLE t1 INCLUDING INDEXES WITHOUT VALIDATION;

-- 驗證
SELECT * FROM t1;
-- 現在 t1 屬於 TEST1，資料完整保留