Oracle XE 資料庫快速匯出匯入

尷尬的年代。AI 模型愈來愈聰明，現在處理電腦問題遇到「不知該怎麼做」，問 AI 十之八九能得到正確解答，我還需要寫成筆記放在部落格上嗎？

想想還是寫吧，自己寫過在腦子裡能留點索引，未來如再遇到會更快進入狀況，而多想多寫也有助減緩大腦退化，就當流汗做重訓吧。

情境是我新裝了一個 Oracle XE，想把另一台 XE 上特定 Shema 下資料表、View、SP... 等一次搬過來。

下載安裝好 XE 後，若為本機自用，我習慣改 Port 並限定本機存取(HOST 原為主機名稱，改成 localhost)，避免被騷擾，做法如下：(以下以 XE 1x 為例，XE 21c 多出 CDB、PDB 概念，做法略有不同。參考)

1. 相關 CLI 工具在 <install-folder>\app\oracle\product\11.2.0\server\bin 或 <install-folder>\21c\dbhomeXE\bin 目錄下
2. 先 lsnrctl stop 停用 Listener
3. 找到 NETWORK\ADMIN\tnsnames.ora，修改設定，Host 改 localhost，Port 改自訂號碼：

   LISTENER =
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 12345)) 
       )
     )
   

4. 重啟 Listener lsnrctl start
5. 使用 sqlplus / as sysdba 登入更新服務註冊 Port：

   ALTER SYSTEM SET LOCAL_LISTENER = "(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=12345))" SCOPE=BOTH;
   ALTER SYSTEM REGISTER;    
   

6. 使用 lsnrctl status 檢查服務是否正常(應要看到 Service "XE" has 1 instance... 資訊)

   Services Summary...
   Service "CLRExtProc" has 1 instance(s).
     Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
   Service "XE" has 1 instance(s).
     Instance "xe", status READY, has 1 handler(s) for this service...
   The command completed successfully
   

接著用 sqlplus / as sysdba 在新 XE 建立帳號：

# ALTER SESSION SET CONTAINER=XEPDB1; # XE 21c 需先切容器
CREATE USER my_new_user IDENTIFIED BY my_password;
-- 授權連線與使用資源
GRANT CONNECT, RESOURCE TO my_new_user;
-- 授權 USERS 空間使用額度 (Quota)，否則無法建立資料表
ALTER USER my_new_user QUOTA UNLIMITED ON USERS;

搬資料庫部分，SQL Developer GUI 工具有 Database Copy 功能，在舊 XE 產生腳本在新 XE 上執行匯入，若資料庫不大，這是最簡單的做法。

但若資料表很多或資料筆數龐大，更有效率的做法是使用 Oracle 提供的 Data Pump (expdp / impdp) 工具。(相當於 MSSQL 的 bcp)

使用 expdp/impdp 前，需在 XE 註冊讀寫匯出檔案的實體目錄，使用 sqlplus / as sysdba 登入執行以下指令：

-- 建立目錄物件 (需確認 D:\db_backup 資料夾已存在)
CREATE OR REPLACE DIRECTORY my_dump_dir AS 'D:\db_backup';
-- 授權給要匯出的帳號
GRANT READ, WRITE ON DIRECTORY my_dump_dir TO 原本的帳號;

使用以下指令在 D:\db_backup 產生 mydata.dmp 及 mydata.log：

expdp schemas=帳號 directory=my_dump_dir dumpfile=mydata.dmp logfile=mydata.log

將這兩個檔案複製到新 XE 主機的資料夾，例如：D:\db_import。匯入端需比照建立目錄物件及授權 READ, WRITE，接著用以下指令以「系統管理員身分」登入，兩 .dmp 及 .log 檔匯入資料庫。

impdp directory=my_import_dir dumpfile=mydata.dmp remap_schema=原帳號:新帳號 logfile=mydata.log

如此就能快速完成 ORACLE 資料庫的匯出匯入囉~