Oracle 按照表條件導出導入資料

-----------------------------------------------------------------------------------

-- 準備工作 --

set line 180

col owner form a10

col directory_name form a30

col directory_path form a85

select * from dba_directories;

create directory exp_dir as 'c:\app\administrator\virtual\backup_dump';

grant read,write on directory exp_dir to public;

---- 導出 ----

-- 按where條件用expdp導出表

-- 示例一

-- emp_main 表需要根據 sendtime 字段導出2018年的資料

-- 配置參數檔案内容如下:

vi emp_main.par

tables=emp_main

dumpfile=emp_main.dmp

logfile=emp_main.log

query="where sendtime between to_date('20180101','yyyymmdd') and to_date('20190101','yyyymmdd')"

-- 執行導出

expdp username/oracle@servicename directory=exp_dir parfile=emp_main.par

-- 示例二

-- 在導出 2018 年的 emp_main 表後，需要導出 emp_detail 中與之關聯的資料，且 exp_detail 表沒有日期字段，

-- 但與 exp_main 通過某字段相關聯

-- 配置參數檔案内容如下

vi emp_detail.par

tables=emp_detail

dumpfile=emp_detail.dmp

logfile=emp_detail.log

query=(emp_detail:"where exists(select 1 from emp_main a where sheetkey = a.id and a.sendtime between to_date('20180101','yyyymmdd') and to_date('20190101','yyyymmdd'))")

-- 執行導出:

expdp username/oracle@servicename directory=exp_dir parfile=emp_detail.par

-- 其他說明: 上述是用expdp指令。如果是exp，則需要将參數檔案中的參數名改為如下：

file=emp_main.dmp

log=emp_main.log

-- 導出指令:

exp username/oracle@servicename parfile=emp_main.par

-- 其他示例

-- 如果要導出多個表的資料，但是對某一個表或者某幾個表的資料有限制

expdp scott/tiger directory=exp_dir dumpfile=a.dmp tables=emp,dept query=/'emp:/"where deptno=20/"/'

expdp scott/tiger directory=exp_dir dumpfile=a1.dmp tables=emp,dept query=/'dept:/"where deptno=20/"/',/'emp:/"where deptno /<=20/"/'

-- 注意，兩個條件可以一起寫，< 、>前面都要加入轉義字元。如果寫在參數檔案中，就不要加入轉義字元，如下所示：

expdp scott/tiger 11g.par

vi 11g.par

directory = exp_dir

dumpfile = exp_tab.dmp

logfile = exp_tab.log

schemas = scott

parallel=2

include = table:"in ('emp','dept')"

query = 'emp:"where deptno =20"','dept:"where deptno <=20"'

----------------------------------------------------------------------------------- 

-- exclude 和 include 參數能夠在使用expdp或impdp是對特定的對象或對象類型進行篩選或過濾。

-- 比如因工作的需要導出特定的表或不導出特定的表、視圖以及存儲過程、索引、限制、授權統計資訊等等。

-- 下面将給出expdp或impdp使用exclude和include參數的方法和示例。

-- 一、exclude/include參數用法：

exclude=[object_type]:[name_clause],[object_type]:[name_clause] -->排出特定對象

include=[object_type]:[name_clause],[object_type]:[name_clause] -->包含特定對象

-- object_type 子句用于指定對象的類型，如table,sequence,view,procedure,package等

-- name_clause 子句可以為sql表達式用于過濾特定的對象名字。它由sql操作符以及對象名(可使用通配符)來過濾指定對象類型中的特定對象。

-- 當未指定name_clause而僅僅指定object_type則所有該類型的對象都将被過濾或篩選。多個[object_type]:[name_clause]中間以逗号分割。

-- 示例：

expdp <other_parameters> schemas=scott exclude=sequence,table:"in ('emp','dept')"

impdp <other_parameters> schemas=scott include=package,function,procedure,table:"='emp'"

-- 二、常用的過濾sql表達式

exclude=sequence,view                          -- 過濾所有的sequence,view

exclude=table:"in ('emp','dept')"               -- 過濾表對象emp,dept

exclude=sequence,view,table:"in ('emp','dept')" -- 過濾所有的sequence,view以及表對象emp,dept

exclude=index:"= 'indx_name'"                   -- 過濾指定的索引對象indx_name

include=procedure:"like 'proc_u%'"              -- 包含以proc_u開頭的所有存儲過程(_ 符号代表任意單個字元)

include=table:"> 'e' "                          -- 包含大于字元e的所有表對象

-- 其它常用操作符 not in, not like, <, != 等

expdp \”/ as sysdba\“  tablespaces=dd estimate_only=y -- estimate_only參數評估導出檔案的大小而不會啟用導出job作業，但是使用estimate參數，則是會啟動job作業，導出檔案

-- 使用query、exclude參數。需要使用par參數檔案。否則會出現一些錯誤, par的參數檔案、導出過程如下：

$ vi exp.par

directory=dump

dumpfile=dump_hr.dmp

content=data_only

exclude=table:"in('countries','locations','regions')"

query=employees:"where department_id!=20 order by employee_id"  

$ expdp hr/hr parfile=exp.par

-- 測試導出 bb.bb_t1 中的部分資料

$ vi expdp_query.par

dumpfile=dump_query.dmp

logfile=dump_query.log

tablespaces=dd

query=bb.bb_t1:"where object_id<10"

$ expdp \“/ as sysdba\” parfile=expdp_query.par

-- 直接将過濾操作符封裝到參數檔案中，如下面的例子

$ vi exp_scott.par

dumpfile = exp_scott_%u.dmp

logfile = exp_scott.log

exclude = table:"in ('emp', 'dept')"

expdp system/oracle parfile=exp.par 

-- 其他 par 示例

directory=dmp

dumpfile=expbig.dmp

tables=(py.order,py.transation)

query=(py.order:"where id<=14000 or id>=134520000",py.transation:"where id<=15000 or id>=8817147")

$ expdp \‘/ as sysdba\’ parfile=exp.par

-- 也可以使用 tables 參數指定分區表的某個分區來泵出分區表的部分資料

tables=(t1:p201805,t1:p201806,t2:p201805,t2:p201806)

impdp \'/ as sysdba\' directory=dump dumpfile=t_exp_01.dmp logfile=t_imp_test.log remap_table=zbb.t_exp:t_imp_test -'

-- 注意這裡remap ，remap後的不要schema，直接remap表 ，否則remap後的表是 zbb.zbb.t_imp_test .比如下面的例子 

impdp \'/ as sysdba\' directory=dump dumpfile=t_exp_01.dmp logfile=t_imp_test.log remap_table=zbb.t_exp:zbb.t_imp_test -'

-- 如下會自動建立remap後的表

impdp \'/ as sysdba\' directory=dump dumpfile=t_exp_01.dmp logfile=t_imp_test.log remap_table=zbb.t_exp:t_imp_test1  -'

---- 導入 ----

-- impdp 按條件導入測試(query)

-- 建立測試表

sql> grant select on "sys"."dba_tables" to c##ahern;

sql> grant dba to c##ahern; -- 這裡隻是cdb中的權限

sql> grant dba to c##ahern container=all; -- 所有pdb都有權限

create table test_table as select * from dba_tables;

-- 導出測試表

expdp c##ahern/oracle directory=exp_dir dumpfile=test_table.dmp logfile=test_table.log tables=c##ahern.test_table

-- 根據條件導入測試表，remap_schema 加 query 條件生效

vi impdp_query_test.par

userid=system/oracle

directory=exp_dir   

dumpfile=test_table.dmp

remap_schema=c##ahern:c##ahern_1   

content=all   

logfile=result_test.log   

tables= c##ahern.test_table

query=(c##ahern.test_table:"where owner ='c##ahern'")

-- 導入

impdp parfile=impdp_query_test.par

impdp c##ahern/oracle directory=exp_dir dumpfile=test_table.dmp content=all logfile=result_test.log tables=test_table query=(test_table:"where owner ='c##ahern'")

impdp c##ahern/oracle directory=exp_dir dumpfile=test_table.dmp content=all logfile=result_test.log remap_table=test_table:test_table_1 query=(test_table:"where owner ='c##ahern'")

impdp system/oracle dumpfile=test_table.dmp directory=exp_dir table_exists_action=append -- 由于上面條件沒有資料所有測試追加資料

--添加remap_table修改表名條件，query條件不生效

remap_schema=c##ahern:c##ahern_1

remap_table=test_table:test_table1  

-- orclpdb 插件資料庫導出導入測試ok

expdp ahern/oracle@localhost:1521/orclpdb directory=exp_dir dumpfile=test.dmp logfile=test.log tables=ahern.test

impdp ahern/oracle@localhost:1521/orclpdb directory=exp_dir dumpfile=test.dmp content=all logfile=imp_test.log tables=test query=test:\"where owner ='ahern'\" -"

impdp ahern/oracle@localhost:1521/orclpdb directory=exp_dir dumpfile=test.dmp content=all logfile=imp_test.log remap_table=ahern.test:test_table query=test:\"where owner ='ahern'\" -"

impdp system/oracle@localhost:1521/orclpdb dumpfile=test.dmp directory=exp_dir table_exists_action=append -- 由于上面條件沒有資料所有測試追加資料

impdp system/oracle@localhost:1521/orclpdb dumpfile=test.dmp remap_table=ahern.test:test_table directory=exp_dir table_exists_action=append