Oracle 經典 DBA 技巧集錦：序列循環、列重命名、刪除列、重復數據清理與 RowID 核心應用

一、 循環序列：自動輪轉的編號生成器

-- 創建循環序列：1 到 999,999,999 循環
CREATE SEQUENCE seq_cycle
  MINVALUE 1
  MAXVALUE 999999999
  START WITH 1
  INCREMENT BY 1
  CYCLE;  -- 關鍵：達到 MAXVALUE 後自動回到 MINVALUE

-- 使用
INSERT INTO t (id, col1) VALUES (seq_cycle.NEXTVAL, 'data');

應用場景： 循環使用的批次號、會話 ID、暫存表主鍵。**業務勿依賴連續性**，重啟/緩存失效會導致間隙。

二、 列重命名：無 ALTER TABLE RENAME COLUMN (11g 前) 的變通方案

11g 前 Oracle 不支援 `ALTER TABLE RENAME COLUMN`，經典三步走：

-- 環境：Oracle 8i/9i/10g
-- 目標：表 t1 列 old_name 重命名為 new_name

-- 1. 創建新表，選取並重命名列
CREATE TABLE t2 AS SELECT old_name AS new_name, col2, col3 FROM t1;

-- 2. 刪除舊表
DROP TABLE t1;

-- 3. 重命名新表
RENAME t2 TO t1;

-- 4. 重建索引/約束/權限/同義詞/觸發器 (關鍵！)
-- 必須從數據字典腳本生成重建腳本

12c+ 原生支援： `ALTER TABLE t1 RENAME COLUMN old_name TO new_name;` (元數據操作，毫秒級，無鎖表)。

三、 刪除列：避開「標記未用/物理刪除」陷阱

經典變通 (8i/9i/10g)：同重命名，CTAS 排除目標列

CREATE TABLE t2 AS SELECT col1, col3 FROM t1; -- 排除 col2
DROP TABLE t1;
RENAME t2 TO t1;
-- 重建依賴對象

10g+ 原生 `SET UNUSED` / `DROP COLUMN`

-- 1. 標記未用 (元數據操作，極快，釋放列鎖，數據仍佔空間)
ALTER TABLE t1 SET UNUSED COLUMN col2;

-- 2. 物理刪除 (需獨佔鎖，全表掃描重寫行，釋放空間)
-- 生產環境建議維護窗口執行
ALTER TABLE t1 DROP UNUSED COLUMNS; 
-- 或一次性
ALTER TABLE t1 DROP COLUMN col2; -- 等價於 SET UNUSED + DROP UNUSED COLUMNS

-- 3. 檢查未用列
SELECT * FROM user_unused_col_tabs WHERE table_name='T1';

避坑： `DROP COLUMN` 會導致所有依賴視圖/PL/SQL 失效，需 `ALTER COMPILE` 重編譯。大表操作耗時久、產生大量 Redo/Undo，評估影響後再行。

四、 重復數據查找與刪除：ROWID 核心應用經典案例

原理：ROWID 唯一標識每行物理位置 (文件/塊/槽)，重復行業務列相同但 ROWID 必異

1. 查找重復 (基於業務鍵)

-- 假設表 a(bm, mc)，業務鍵為 bm
SELECT bm, COUNT(*) 
FROM a 
GROUP BY bm 
HAVING COUNT(*) > 1;

2. 刪除重復，保留 ROWID 最大 (或最小) 的那行

-- 經典寫法：子查詢 + ROWID 比較
DELETE FROM a
WHERE rowid NOT IN (
  SELECT MAX(rowid) 
  FROM a 
  GROUP BY bm
);
-- 保留每組 bm 中 ROWID 最大的一行 (通常為最後插入)

3. 三大技巧融合：ROWID + 子查詢 + 別名

-- 使用別名區分外部/內部表引用
DELETE FROM a a1
WHERE a1.rowid > (
  SELECT MIN(a2.rowid) 
  FROM a a2 
  WHERE a2.bm = a1.bm
);
-- 保留每組 ROWID 最小的一行

4. 大表高性能刪除重復 (CTAS 交換法)

-- 1. 創建去重新表 (並行、無日志加速)
CREATE TABLE a_dedup NOLOGGING PARALLEL 4 AS
SELECT * FROM (
  SELECT a.*, ROW_NUMBER() OVER (PARTITION BY bm ORDER BY rowid) rn
  FROM a
) WHERE rn = 1;

-- 2. 重建索引/約束/權限
-- 3. 交換表名 (需短暫鎖表)
RENAME a TO a_old;
RENAME a_dedup TO a;

-- 4. 刪除舊表釋放空間
DROP TABLE a_old PURGE;

優勢： 避免大量 DELETE 產生的 Undo/Redo、高水位線不下降、索引碎片化。`NOLOGGING` + `PARALLEL` 極大加速。

五、 ROWID 進階應用：分塊並行處理大表

-- 將大表按 ROWID 範圍分片，多會話並行處理 (如批量更新、導出)
SELECT 'UPDATE large_table SET col = val WHERE rowid BETWEEN ''' 
       || MIN(rowid) || ''' AND ''' || MAX(rowid) || ''';' AS stmt
FROM (
  SELECT rowid, NTILE(8) OVER (ORDER BY rowid) AS bucket
  FROM large_table
)
GROUP BY bucket
ORDER BY bucket;
-- 生成 8 條 UPDATE 語句，各佔 1/8 行，8 個會話並行跑