Oracle 常見問題解答大全：分頁查詢、表維護、權限、鎖定與進階管理

一、SELECT N 分頁查詢技巧

Oracle 無 LIMIT，常用 ROWNUM 配合子查詢實現：

• TOP N：SELECT * FROM (SELECT * FROM cat ORDER BY table_type) WHERE ROWNUM <= N;
• N1 到 N2 行：SELECT * FROM (SELECT ROWNUM rn, t.* FROM cat t) WHERE rn BETWEEN N1 AND N2;
• BOTTOM N：SELECT * FROM (SELECT * FROM cat ORDER BY table_type DESC) WHERE ROWNUM <= N;

二、表空間移動與碎片整理

-- Oracle 8i 以上支援
ALTER TABLE table_name MOVE TABLESPACE new_tablespace;
-- 需重建索引
ALTER INDEX index_name REBUILD;

傳統 Export/Drop/Create/Import 需更多空間與時間，且會導致 PL/SQL 物件失效需重新編譯。

三、SQL*Plus 實用技巧

• 免帳號登入：sqlplus /nolog 再 CONN user/pass@sid
• 快速重建索引：ALTER INDEX idx REBUILD; 或 COALESCE
• 啟用 DBMS_OUTPUT：SET SERVEROUTPUT ON
• 指定大型交易使用特定回滾段：SET TRANSACTION USE ROLLBACK SEGMENT rbs_name; 或 PL/SQL 中 DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT

四、資料類型選擇：VARCHAR2 vs CHAR

• CHAR 固定長度、最大 2000 位元組、自動填空格、佔用較多空間。
• VARCHAR2 變動長度、最大 4000 位元組、僅佔用實際資料長度、效能較佳。

五、空間使用量差異：DBA_TABLES vs DBA_SEGMENTS

DBA_TABLES.BLOCKS = 實際使用區塊，EMPTY_BLOCKS = 配置但無資料區塊。DBA_SEGMENTS.BLOCKS = 總配置區塊 = BLOCKS + EMPTY_BLOCKS。

六、刪除重複資料

DELETE FROM emp a
WHERE (emp_id, office_id, 2) IN (
  SELECT emp_id, office_id, DECODE(COUNT(*),1,1,2)
  FROM emp b
  WHERE a.emp_id=b.emp_id AND a.office_id=b.office_id
  GROUP BY emp_id, office_id
);

七、其他常見操作

• 清空表：TRUNCATE TABLE table_name; (重置 HIGH WATER MARK、釋放空間)
• Drop 欄位 (8i+)：ALTER TABLE t DROP COLUMN col; 需 COMPATIBLE=8.1.0
• 重命名欄位：新增欄位 → 複製資料 → 刪除舊欄位
• 查詢表權限：SELECT * FROM dba_tab_privs WHERE table_name='...';
• 找出鎖表會話：SELECT object_id FROM v$locked_object; 再查 dba_objects
• 判斷實例是否啟動：Unix 下 ps -ef | grep pmon；Windows 看服務狀態
• 資料檔自動擴展：8i 後查 DBA_DATA_FILES.AUTOEXTENSIBLE
• 資料庫級觸發器 (8i+)：啟動/關閉、登入/登出事件
• SHUTDOWN TRANSACTIONAL：等待所有交易提交後關閉
• 全域暫存表 (8+)：CREATE GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE/DELETE ROWS
• 反序索引：CREATE INDEX idx ON t(col) REVERSE; 適合 RAC 減少熱塊爭用

八、回滾段 OPTIMAL 參數設定建議

• 長交易多：設較大 OPTIMAL，減少擴縮碎片。
• 短交易為主：設較小 OPTIMAL，讓資料留在 SGA 提升效能。
• 未設 OPTIMAL 時，COMMIT 後不自動收縮，需手動 ALTER ROLLBACK SEGMENT rbs SHRINK TO size;