您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 使用者管理與資料匯入匯出實戰手冊

編輯：Oracle 資料庫基礎

前言：從建立使用者到跨環境遷移的標準化流程

使用者管理與資料移動是 DBA 日常最高頻操作。本文整理從建立使用者、表空間規劃、權限授予、密碼策略到 exp/imp 與 expdp/impdp 實戰參數，提供可直接複製執行的標準化腳本與避坑指南。

一、使用者與表空間標準化建立

1. 建立表空間（採用 AUTOEXTEND + 本地管理）

-- 臨時表空間
CREATE TEMPORARY TABLESPACE app_temp
  TEMPFILE '/u01/app/oracle/oradata/ORCL/app_temp01.dbf'
  SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE 4G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- 資料表空間
CREATE TABLESPACE app_data
  LOGGING
  DATAFILE '/u01/app/oracle/oradata/ORCL/app_data01.dbf'
  SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 50G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

2. 建立使用者並指定預設/臨時表空間

CREATE USER app_user IDENTIFIED BY "ComplexP@ssw0rd"
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE app_temp
  PROFILE app_profile;  -- 綁定密碼複雜度 Profile

-- 修改資料庫預設表空間（影響後續無指定 TABLESPACE 的使用者）
ALTER DATABASE DEFAULT TABLESPACE app_data;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE app_temp;

3. 標準權限授予模板

-- 基礎角色
GRANT CREATE SESSION TO app_user;
GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE,
      CREATE TRIGGER, CREATE TYPE, CREATE SYNONYM TO app_user;

-- 資源角色（含 UNLIMITED TABLESPACE，生產環境建議改用 QUOTA）
GRANT RESOURCE TO app_user;

-- 表空間配額（替代 UNLIMITED TABLESPACE）
ALTER USER app_user QUOTA UNLIMITED ON app_data;
ALTER USER app_user QUOTA 100M ON app_temp;

二、密碼與帳號安全管理

-- 解鎖帳號
ALTER USER scott ACCOUNT UNLOCK;

-- 密碼驗證函數綁定
CREATE PROFILE app_profile LIMIT
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LIFE_TIME 90
  PASSWORD_REUSE_TIME 365
  PASSWORD_REUSE_MAX 10
  PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;

-- 檢查預設密碼帳號
SELECT username FROM dba_users_with_defpwd WHERE account_status = 'OPEN';

三、傳統 exp/imp 實戰參數大全

注意：11g 起官方推薦 Data Pump (expdp/impdp)，exp/imp 僅支援舊版相容，不支援壓縮、並行、網路模式等新特性。

匯出常用模式

場景	命令範例
完整資料庫	`exp system/pass@ORCL file=/backup/full.dmp full=y compress=y`
指定 Schema	`exp app_user/pass@ORCL file=/backup/schema.dmp owner=app_user`
指定資料表	`exp app_user/pass@ORCL file=/backup/tables.dmp tables=(orders,order_items)`
條件式匯出	`exp app_user/pass@ORCL file=/backup/cond.dmp tables=orders query="WHERE status='OPEN' AND created_dt > SYSDATE-30"`
僅匯出中繼資料	`exp app_user/pass@ORCL file=/backup/meta.dmp rows=n`

匯入關鍵參數

-- 完整匯入（忽略已存在物件錯誤）
imp system/pass@ORCL file=/backup/full.dmp full=y ignore=y

-- Schema 級匯入（重新對應使用者）
imp system/pass@ORCL file=/backup/schema.dmp fromuser=app_user touser=app_user_new

-- 單表匯入（覆蓋模式）
imp app_user/pass@ORCL file=/backup/tables.dmp tables=orders ignore=y

-- 只匯入結構
imp app_user/pass@ORCL file=/backup/meta.dmp rows=n

四、 Data Pump (expdp/impdp) 現代化遷移

目錄物件與權限

CREATE DIRECTORY dpump_dir AS '/u01/dpump';
GRANT READ, WRITE ON DIRECTORY dpump_dir TO app_user;

高效能並行匯出/匯入

# 匯出：並行 4、壓縮中繼資料、排除統計資訊
expdp app_user/pass@ORCL \
  directory=dpump_dir \
  dumpfile=app_schema_%U.dmp \
  logfile=expdp_app.log \
  schemas=app_user \
  parallel=4 \
  compression=METADATA_ONLY \
  exclude=STATISTICS \
  filesize=2G

# 匯入：並行、重對應表空間、轉換 SEGMENT 屬性
impdp app_user/pass@ORCL \
  directory=dpump_dir \
  dumpfile=app_schema_%U.dmp \
  logfile=impdp_app.log \
  schemas=app_user \
  remap_tablespace=app_data:app_data_ssd \
  transform=SEGMENT_ATTRIBUTES:N \
  table_exists_action=REPLACE \
  parallel=4

網路模式直接跨庫遷移（無需 Dump 檔）

# 來源建立 DB LINK
CREATE PUBLIC DATABASE LINK src_link CONNECT TO app_user IDENTIFIED BY pass USING 'SRC_DB';

# 目標直接匯入
impdp app_user/pass@TGT_DB \
  directory=dpump_dir \
  network_link=src_link \
  schemas=app_user \
  remap_tablespace=app_data:app_data_ssd \
  parallel=4 \
  logfile=net_imp.log

五、進階場景：擁有者變更與跨版本遷移

參數檔方式（import.par）

-- import.par 內容
userid=system/pass@TGT
fromuser=OLD_OWNER
touser=NEW_OWNER
schemas=OLD_OWNER
remap_tablespace=OLD_TS:NEW_TS
transform=OID:N,SEGMENT_ATTRIBUTES:N
table_exists_action=SKIP
parallel=4
logfile=remap.log

impdp parfile=import.par

六、常見錯誤與解決方案

錯誤	原因	解決
ORA-39002: invalid operation	版本不相容（高版本 dump 低版本導入）	使用 `VERSION=11.2` 參數匯出，或升級目標庫
ORA-39083: Object grant failed	權限不足或角色不存在	先建立角色、GRANT 系統權限，或 `EXCLUDE=GRANT` 後手動授權
IMP-00010: not a valid export file	檔案損壞/傳輸模式錯誤	FTP 必須用 BINARY 模式，檢查 checksum
ORA-01555 snapshot too old	匯出一致性讀需 UNDO 保留不足	增加 `UNDO_RETENTION`、使用 `FLASHBACK_TIME`、離峰執行
ORA-39151: Table exists	目標表已存在且無 `table_exists_action`	加 `table_exists_action=REPLACE\|SKIP\|APPEND\|TRUNCATE`

七、驗證清單

物件計數對比：SELECT owner, object_type, COUNT(*) FROM dba_objects GROUP BY owner, object_type;
資料筆數抽樣：核心表 COUNT(*) 對比
約束/索引狀態：SELECT * FROM dba_constraints WHERE status='DISABLED';
統計資訊收集：DBMS_STATS.GATHER_SCHEMA_STATS
應用煙霧測試：關鍵業務流程端到端驗證

遷移不只是匯入匯出，驗證才是交付的開始。建立標準化遷移 Runbook，每次遷移留存日誌與驗證報告，才是專業 DBA 的標誌。