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，则需要将参数文件中的参数名改为如下：

vi emp_detail.par

tables=emp_main

file=emp_main.dmp

log=emp_main.log

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

-- 导出命令:

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

directory=dump

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

DIRECTORY = exp_dir

DUMPFILE = exp_scott_%U.dmp

LOGFILE = exp_scott.log

SCHEMAS = scott

PARALLEL=2

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条件不生效

vi impdp_query_test.par

userid=system/oracle

DIRECTORY=exp_dir   

DUMPFILE=test_table.dmp

remap_schema=C##AHERN:C##AHERN_1

remap_table=test_table:test_table1  

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

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

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

-- 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

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