> expdp/impdp

- [資料泵最基本的語句](#資料泵最基本的語句)
- [檢查相關SQL](#檢查相關sql)
- [匯出語句參考](#匯出語句參考)
- [匯入語句參考](#匯入語句參考)
  - [合併多個表空間](#合併多個表空間)
  - [收集統計資訊](#收集統計資訊)
- [提升效能](#提升效能)
- [作業情況檢視](#作業情況檢視)
- [DBLINK](#dblink)
- [附：TRANSFORM引數介紹](#附transform引數介紹)
- [參考](#參考)

#### 資料泵最基本的語句

``` shell
mkdir -p /home/oracle/datadump
create directory exp_dmp as '/home/oracle/datapump';
grant read,write on directory exp_dmp to <username>;
export <username>/<password> dumpfile=<username>_dump2021.dmp logfile=<username>_expdp2021.log directory=exp_dmp
```
上面只是舉例匯出一個使用者資料，當然資料泵有很多功能，也有很多引數讓使用資料泵遷移資料提供了便利。例如更換使用者、表空間、排除一些物件（如統計資訊、表等）、指定一些物件等。

#### 檢查相關SQL
``` sql
--檢視字符集
select userenv('language') from dual;
export NLS_LANG="以上查詢結果" -- 目的不是這個，是為了目標端匹配源端
--查詢當前空表11g+
set heading off; 
set echo off; 
set feedback off; 
set termout on; 
spool C:\allocate.sql; 
Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0; 
spool off;

--檢視需要遷移使用者
select d.username,u.password,d.default_tablespace,d.TEMPORARY_TABLESPACE
from dba_users d,user$ u
where d.username=u.name and account_status='OPEN'
and username not in('SYS','SYSTEM','DBSNMP','SYSMAN','MGMT_VIEW');

--檢視同義詞
SELECT 'CREATE  or replace PUBLIC SYNONYM '||synonym_name||' FOR '||table_owner||'.'||table_name||';' 
FROM dba_SYNONYMS WHERE table_owner in ('scott','hr','system' )  AND owner='PUBLIC';

--查詢有些的排除或包含的值(三個檢視，資料庫、使用者、表),下面例子是資料庫級別
select object_path,comments from database_export_objects
where object_path like '%CONSTRAINT%' and object_path not like '%/%';
```

#### 匯出語句參考
``` shell
#新增時間戳
logtime=all
#匯出匯入物件顯示時間
metrics=yes

#並行匯出
expdp '/ as sysdba' directory=my_exp dumpfile=myexpdb%U.dmp logfile=myexpdb.log parallel=2 EXCLUDE=STATISTICS parfile=mypar.par cluster=no

#mypar.par m注意可以排除系統使用者及非OPEN使用者  全庫匯出無需建立使用者
FULL=Y
EXCLUDE=SCHEMA:"in (select username from dba_users where account_status<>'OPEN' or username in('SYS','SYSTEM'))"

#排除部分使用者表
expdp sys/a directory=exp_dmp dumpfile=scott.dmp logfile=scott1.log schemas=scott 
EXCLUDE=table:\"in $select table_name from dba_tables where owner\=\'SCOTT\' and table_name\=\'EMP\'$\"

#匯出全庫，壓縮
expdp \'/ as sysdba\' directory=dump_dir dumpfile=full_sys_all.dmp logfile=expdp_full_sys_all.log parallel=2 full=y COMPRESSION=all

#只匯出public的dblink
include=db_lin:"in (select db_link from dba_db_links where owner='PUBLIC')"

#只匯出指定表的20%
schemas=<username> sample=<tablename>:20

#12c之後檢視可以作為表匯出
views_as_tables=scott.v_emp_dept
```

#### 匯入語句參考
``` shell
#匯入
impdp \'/ as sysdba\' directory=full_dmp dumpfile=dumpfull_2019%U.dmp logfile=dumpfull_2019_imp.log parallel=4 TABLE_EXISTS_ACTION=SKIP exclude=TABLESPACE cluster=N

#分割槽表匯入到非分割槽表
partitions_options=merge
#分割槽表匯入到單獨表（每個分割槽一個表）
partition_options=departition

#使用預設的儲存引數
transform=segment_attributes:N
```
##### 合併多個表空間
``` shell
#說明：以下是源端多個表空間，目標端資料和索引分為兩個表空間的匯入方式

#匯入表
exclude=index
transform=segment_attributes:N

#匯入索引,使用sqlfile，生成索引建立語句，更改索引名字
include=index
transform=storage:N
sqlfile=xxx_index.sql
#另一種辦法是暫時將使用者預設表空間修改為xxx_index
include=index
transform=segment_attributes:N
```
> TRANSFORM 引數介紹請參見《附：TRANSFORM引數介紹》

##### 收集統計資訊

``` shell
begin
dbms_stats.gather_database_stats(
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns size auto', cascade=>true, degree=>8);
end;
/
```

#### 提升效能

- 1.並行執行
- 2.不生成歸檔日誌12C之後 (注意，如果資料庫時force logging，此引數會失效)
``` shell
  transform=disable_archive_logging:Y
```
- 3.含有lob的大表單獨匯出（可單獨拆分匯出，提高效率）
- 4.壓縮，尤其磁碟、網路資源限制時，通過消耗cpu方式來提高效率（注意，一般與並行引數一起）
- 5.效能相關引數

``` shell
  DISK_ASYNCH_IO=TRUE
  DB_BLOCK_CHECKING=FALSE
  B_BLOCK_CHECKSUM=FALSE
  #另外需要注意，19c之後，每個pdb同時執行資料泵作業限制
  MAX_DATAPUMP_JOBS_PER_PDB 
  MAX_DATAPUMP_PARALLEL_PER_JOB #每個作業最大並行數
```

#### 作業情況檢視
``` sql
-- 查詢資料泵作業:
set lines 150
set pages 999
col owner_name for a15
col job_name for a30
col operation for a40
col job_mode for a30
col state for a15
col attached_sessions for 20
SELECT owner_name, job_name, operation, job_mode, 
state
--, attached_sessions 
FROM dba_datapump_jobs 
WHERE job_name NOT LIKE 'BIN$%' 
ORDER BY 1,2;

--啟停作業
expdp sys/firsoul ATTACH=SYS_EXPORT_SCHEMA_01
stop_job/start_job

-- 查詢資料泵的 master 表,停止無法刪除
set lines 150
col OWNER.OBJECT for a40
SELECT o.status, o.object_id, o.object_type, 
       o.owner||'.'||object_name "OWNER.OBJECT" 
  FROM dba_objects o, dba_datapump_jobs j 
WHERE o.owner=j.owner_name AND o.object_name=j.job_name 
   AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

--檢視作業情況
SET LINE 9999
COL OWNER_NAME FOR A10
COL JOB_NAME FOR A25
COL OPERATION FOR A10
COL JOB_MODE FOR A10
COL STATE FOR A15
COL OSUSER FOR A10
COL "DEGREE|ATTACHED|DATAPUMP" FOR A25
COL SESSION_INFO FOR A20  
SELECT DS.INST_ID,
       DJ.OWNER_NAME,
       DJ.JOB_NAME,
       TRIM(DJ.OPERATION) OPERATION,
       TRIM(DJ.JOB_MODE) JOB_MODE,
       DJ.STATE,
       DJ.DEGREE || ',' || DJ.ATTACHED_SESSIONS || ',' ||DJ.DATAPUMP_SESSIONS "DEGREE|ATTACHED|DATAPUMP",
       DS.SESSION_TYPE,
       S.OSUSER ,
       (SELECT S.SID || ',' || S.SERIAL# || ',' || P.SPID
          FROM GV$PROCESS P
         WHERE S.PADDR = P.ADDR
           AND S.INST_ID = P.INST_ID) SESSION_INFO
  FROM DBA_DATAPUMP_JOBS DJ --GV$DATAPUMP_JOB  
  FULL OUTER JOIN DBA_DATAPUMP_SESSIONS DS --GV$DATAPUMP_SESSION
    ON (DJ.JOB_NAME = DS.JOB_NAME AND DJ.OWNER_NAME = DS.OWNER_NAME)
  LEFT OUTER JOIN GV$SESSION S
    ON (S.SADDR = DS.SADDR AND DS.INST_ID = S.INST_ID)
 ORDER BY DJ.OWNER_NAME, DJ.JOB_NAME;
```

#### DBLINK
``` sql
create public database link dblink1 connect to mytest identified by test
  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.8.24)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME=mydb)))';

DROP PUBLIC DATABASE LINK dblink2;
select sysdate from dual@dblink1;

--匯出
impdp system/oracle directory=sh_dmp network_link=dblink1 remap_schema=mytest:test remap_tablespace=users:test
```

#### 附：TRANSFORM引數介紹
transform_name是可選的。如果提供，此引數將指定應用變換的物件型別。如果未指定物件型別，則變換將應用於所有有效的物件型別,格式參考
``` shell
TRANSFORM = transform_name:value[:object_type]
```
可用的轉換按字母順序如下所示：
- CONSTRAINT_NAME_FROM_INDEX: [Y|N]  **如果設定為Y，則強制約束的名稱與索引的名稱匹配。**
- CONSTRAINT_USE_DEFAULT_INDEX: [Y|N]  **如果設定為Y，則transform引數將強制自動建立的索引的名稱，以強制約束與約束名稱相同**
- DISABLE_ARCHIVE_LOGGING:[Y|N] **如果設定為Y，則匯入不生成歸檔，資料庫設定force logging模式除外**
- LOB_STORAGE:[SECUREFILE|BASICFILE|DEFAULT|NO_CHANGE]  **LOB段是使用指定的儲存（SECUREFILE或BASICFILE）建立的。**
- SEGMENT_ATTRIBUTES:[Y|N] **包含段屬性（物理屬性、儲存屬性、表空間和日誌記錄），並帶有適當的DDL。預設值為Y(就是包含)**
- SEGMENT_CREATION:[Y|N] **建表時預設建立延遲段，在建表語句中新增**
- STORAGE:[Y|N] **如果將該值指定為Y，則包含儲存子句，幷包含適當的DDL。預設值為Y。如果段屬性N，則忽略此引數**
  
舉例說明
``` shell
#匯入表時，為目標使用者預設表空間
TRANSFORM=SEGMENT_ATTRIBUTES:N:table
```
#### 參考
- 官方手冊：https://docs.oracle.com/en/database/oracle/oracle-database/21/sutil/oracle-datapump-import-utility.html#GUID-4F26914E-28D2-428C-9F20-256A3C861068
- 《Oracle資料庫問題解決方案和故障排除手冊》：13章</tablename></username></username></username></password></username></username>

【DATAPUMP】Oracle資料泵優化及提高效率的一些引數介紹

相關文章