SQL*Plus COPY 指令可透過 SQL*Net 在同一或不同伺服器的 Schema/資料庫間複製資料，是 DBA 遷移、同步、備份資料的輕量級工具。本文詳細說明語法、四種模式、跨 Schema 寫法、效能優化參數與常見錯誤處理。
COPY 指令語法結構
COPY {FROM db | TO db | FROM db TO db}
{APPEND | CREATE | INSERT | REPLACE}
dest_table [(col1, col2, ...)]
USING query
關鍵子句解析
| 子句 | 說明 | 適用情境 |
|---|
| FROM db | 來源資料庫 user/pwd@sid | Pull 模式：從遠端拉資料 |
| TO db | 目標資料庫 user/pwd@sid | Push 模式：推送到遠端 |
| FROM db TO db | 雙向指定（跨 Schema 必須） | 來源/目標不同使用者或不同 DB |
| APPEND | 目標表存在則追加，不存在則建立 | 增量同步、合併資料 |
| CREATE | 建立目標表並寫入，表存在則報錯 | 初次遷移、建立測試資料 |
| INSERT | 目標表必須存在，僅插入資料 | 定期匯入、不想自動建表 |
| REPLACE | Drop 目標表重建並寫入 | 全量覆蓋、結構同步 |
實戰範例
1. 同一資料庫不同 Schema 複製（同機器）
-- 以 system 登入 SQL*Plus
COPY FROM scott/tiger@orcl TO hr/hr@orcl
CREATE emp_copy USING SELECT * FROM emp WHERE deptno = 10;
2. 跨伺服器全量同步（REPLACE 模式）
COPY FROM src_user/src_pwd@src_db TO tgt_user/tgt_pwd@tgt_db
REPLACE sales_fact
USING SELECT * FROM sales_fact WHERE sale_date >= TRUNC(SYSDATE) - 30;
3. 指定欄位順序與別名
COPY FROM a/b@db1 TO c/d@db2
INSERT target_tbl (id, name, amt, dt)
USING SELECT seq.NEXTVAL, cust_name, total_amt, SYSDATE FROM staging_tbl;
效能優化關鍵參數
SET ARRAYSIZE 5000：每次網路往返傳輸列數（預設 15，調大可大幅降低 Round-trip）。SET COPYCOMMIT 100：每 100 批次 Commit 一次（預設 0 不中途 Commit），平衡 Undo 產生與失敗回滾成本。SET LONG 2000000000：支援 CLOB/LONG 欄位複製。- 來源查詢加
/*+ PARALLEL(4) */ 並行掃描（Enterprise Edition）。
常見錯誤與解決
| 錯誤 | 原因 | 解決 |
|---|
| CPY-0002: Invalid username/password | 連線字串錯誤、TNS 未設定 | 確認 tnsnames.ora、測試 tnsping |
| CPY-0007: Table or view does not exist | 來源查詢表名錯誤或權限不足 | 授予 SELECT ANY TABLE 或完整指定 Schema |
| ORA-01555: Snapshot too old | 長時間複製遭遇 Undo 覆寫 | 加大 UNDO_RETENTION、分批次複製、避開高峰期 |
| 資料型態不符 | 隱含轉換失敗（如字串塞數值） | USING 查詢中顯式 TO_NUMBER/TO_DATE、指定欄位清單 |
COPY vs 其他方案快速比較
| 方案 | 優點 | 缺點 | 建議場景 |
|---|
| SQL*Plus COPY | 無需額外工具、跨版本/平台、指令簡單 | 無並行寫入、大量資料較慢、無錯誤重試機制 | 中小量資料（< 100萬列）、臨時遷移、跨版本 |
| Data Pump (expdp/impdp) | 並行、壓縮、過濾、元資料完整 | 需目錄物件、版本相容性限制 | 大量資料、整個 Schema/表空間遷移 |
| DB Link + INSERT | SQL 原生、可加 Hint、交易控制 | 跨 DB 需建立 DB Link、分散式交易風險 | 頻繁同步、ETL 流程 |
| GoldenGate / XStream | 即時 CDC、零停機、異構支援 | 架構複雜、授權成本高 | 核心業務即時同步、異地災備 |
善用 COPY 指令配合 ARRAYSIZE/COPYCOMMIT，能以最低成本完成跨環境資料複製任務。
