使用SQL*Plus進行Oracle數據遷移的指南

一、數據遷移前的準備工作

1. 權限確認：確保具備源數據庫和目標數據庫的讀權限（導出數據）及寫權限（導入數據）。若遷移其他用戶的對象（如表、視圖），需具備EXP_FULL_DATABASE（導出）或IMP_FULL_DATABASE（導入）權限。
2. 工具與環境配置：
   • 在源/目標服務器上安裝Oracle客戶端，確保sqlplus、expdp（數據泵導出）、impdp（數據泵導入）命令可用。
   • 配置tnsnames.ora文件（位于$ORACLE_HOME/network/admin），添加源/目標數據庫的連接別名（如SOURCE_DB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=source_host)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=source_svc)))），便于sqlplus通過別名連接。
3. 數據庫結構理解：通過USER_TABLES、ALL_CONSTRAINTS、ALL_INDEXES等視圖，明確源數據庫中需遷移的表、索引、約束、觸發器等對象信息，避免遺漏關鍵數據。
4. 遷移需求定義：明確需遷移的表范圍（全表/部分表）、數據量大?。ㄔu估遷移時間）、遷移時間窗口（盡量選擇業務低峰期）、是否允許目標數據庫寫入（如是否允許INSERT沖突）。

二、使用SQL*Plus導出數據（平面文件方式）

若需將數據導出為CSV、TXT等平面文件（適用于跨數據庫遷移或后續處理），可通過SQL*Plus的SPOOL命令實現：

1. 編寫導出腳本（如export_emp.sql）：

   SET PAGESIZE 0              -- 不顯示分頁頁眉頁腳
   SET FEEDBACK OFF            -- 不顯示查詢結果的反饋信息（如"X rows selected"）
   SET HEADING OFF             -- 不顯示列標題
   SET MARKUP HTML OFF         -- 不生成HTML格式
   SET ECHO OFF                -- 不顯示執行的SQL語句
   SET VERIFY OFF              -- 不顯示變量替換后的語句
   SET TERMOUT OFF             -- 不顯示命令輸出到屏幕
   SET TRIMSPOOL ON            -- 去除輸出內容的尾部空格
   SPOOL /path/to/output_emp.csv  -- 指定輸出文件路徑（Linux/Unix用絕對路徑）
   SELECT emp_id || ',' || emp_name || ',' || salary FROM emp;  -- 使用分隔符拼接字段（CSV格式）
   SPOOL OFF                   -- 結束輸出，關閉文件
   

2. 執行導出腳本：登錄sqlplus后，運行@/path/to/export_emp.sql，即可將emp表的數據導出為CSV文件。

三、使用數據泵工具（EXPDP/IMPDP）遷移數據

數據泵（Data Pump）是Oracle推薦的快速數據遷移工具，支持增量遷移、并行處理、壓縮等功能，效率遠高于傳統EXP/IMP。需通過操作系統命令行執行（而非sqlplus內部），但sqlplus可用于創建數據泵所需的目錄對象。

1. 創建數據泵目錄對象：
   登錄sqlplus（需具備CREATE ANY DIRECTORY權限），執行以下命令創建目錄對象（關聯操作系統路徑）：

   CREATE DIRECTORY export_dir AS '/u01/app/oracle/dumpfiles';
   CREATE DIRECTORY import_dir AS '/u01/app/oracle/dumpfiles';
   

   執行SELECT * FROM dba_directories;驗證目錄是否創建成功。
2. 導出數據（EXPDP）：
   在操作系統命令行（切換至oracle用戶，因普通用戶無權限執行數據泵命令）運行：

   expdp 'scott/tiger@source_db' schemas=scott directory=export_dir dumpfile=scott_data.dmp logfile=expdp_scott.log
   

   參數說明：
   • schemas=scott：指定導出的用戶方案（僅導出scott用戶的對象）；
   • directory=export_dir：指定導出文件的存儲目錄（需與sqlplus中創建的目錄對象一致）；
   • dumpfile=scott_data.dmp：導出文件名；
   • logfile=expdp_scott.log：導出日志文件名。
3. 傳輸導出文件：將生成的.dmp文件從源服務器傳輸至目標服務器（使用scp、FTP等工具），例如：

   scp /u01/app/oracle/dumpfiles/scott_data.dmp target_user@target_host:/u01/app/oracle/dumpfiles/
   

4. 導入數據（IMPDP）：
   在目標服務器的操作系統中運行：

   impdp 'system/manager@target_db' schemas=scott directory=import_dir dumpfile=scott_data.dmp logfile=impdp_scott.log
   

   若需將數據導入至目標數據庫的其他用戶（如target_user），需添加touser參數：

   impdp 'system/manager@target_db' schemas=scott directory=import_dir dumpfile=scott_data.dmp logfile=impdp_scott.log touser=target_user
   

   參數說明：
   • touser=target_user：指定目標用戶（需具備IMP_FULL_DATABASE權限）；
   • 其他參數含義與EXPDP一致。

四、使用SQL*Plus直接遷移數據（跨數據庫）

若源/目標數據庫均在同一網絡且已配置tnsnames.ora，可通過SQL*Plus的COPY命令直接遷移數據（無需中間文件），適用于小批量數據遷移：

-- 從源數據庫復制數據至目標數據庫（創建目標表并插入數據）
COPY FROM scott/tiger@source_db TO scott/tiger@target_db 
      CREATE emp_target 
      USING SELECT * FROM emp_source;

-- 向目標數據庫現有表插入數據（目標表需提前存在）
COPY FROM scott/tiger@source_db TO scott/tiger@target_db 
      INSERT emp_target 
      USING SELECT * FROM emp_source;

-- 替換目標表（刪除目標表并重新創建，再插入數據）
COPY FROM scott/tiger@source_db TO scott/tiger@target_db 
      REPLACE emp_target 
      USING SELECT * FROM emp_source;

參數說明：

• FROM/TO：指定源/目標數據庫的連接信息（username/password@tns_alias）；
• CREATE：目標表不存在時創建表（結構與源表一致）；
• INSERT：向目標表插入數據（目標表需存在）；
• REPLACE：刪除目標表并重新創建（適用于表結構變更場景）；
• USING：指定源數據的查詢語句。

五、數據驗證與清理

1. 數據驗證：
   • 數量驗證：對比源/目標表的行數（SELECT COUNT(*) FROM emp;），確保數據量一致；
   • 內容驗證：抽樣查詢關鍵字段（如SELECT emp_id, emp_name FROM emp WHERE emp_id = 100;），確認數據準確性；
   • 約束驗證：檢查目標表的約束（如主鍵、唯一鍵、外鍵）是否生效（SELECT * FROM user_constraints WHERE table_name = 'EMP';）。
2. 清理工作：
   • 刪除臨時平面文件（如/path/to/output_emp.csv）；
   • 刪除數據泵導出文件（如scott_data.dmp）；
   • 刪除sqlplus日志文件（如export_emp.log），釋放存儲空間。

注意事項

• 字符集一致性：源/目標數據庫的字符集需一致（如AL32UTF8），否則可能導致中文亂碼（EXPDP/IMPDP時會自動檢查字符集，不一致時報錯）。
• 權限問題：EXPDP/IMPDP需在oracle用戶下執行，普通用戶無法調用；COPY命令需源/目標數據庫的SELECT（源）和INSERT（目標）權限。
• 大對象處理：若表包含BLOB、CLOB等大對象，建議使用EXPDP/IMPDP（支持大對象高效遷移），而非COPY或平面文件。
• 錯誤處理：遷移過程中若出現錯誤（如ORA-39002: invalid operation、ORA-12154: TNS:could not resolve），需檢查日志文件（如expdp_scott.log）定位問題（如權限不足、目錄不存在、TNS配置錯誤）。