Oracle 按照表条件导出导入数据

简介: ------------------------------------------------------------------------------------- 准备工作 --set line 180col OWNER form a10col DIRECTORY_NAME fo...

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

-- 准备工作 --

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

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

目录
相关文章
|
13天前
|
SQL 分布式计算 Oracle
使用Sqoop从Oracle数据库导入数据
使用Sqoop从Oracle数据库导入数据
使用Sqoop从Oracle数据库导入数据
|
6月前
Oracle11G用EXP导出时,空表不能导出解决
Oracle11G用EXP导出时,空表不能导出解决
|
7月前
|
Oracle 关系型数据库 数据库
9-2 Oracle数据库(表)的逻辑备份与恢复 --导出与导入
9-2 Oracle数据库(表)的逻辑备份与恢复 --导出与导入
106 1
|
8月前
|
存储 SQL Oracle
Oracle数据库批量删除表、视图、序列、存储过程、函数脚本
Oracle数据库批量删除表、视图、序列、存储过程、函数脚本
74 0
|
13天前
|
Oracle 关系型数据库 数据库
Oracle查询优化-复制表的定义及数据
【1月更文挑战第5天】【1月更文挑战第14篇】在Oracle数据库中,复制表定义和复制表数据是两个常见的操作。
59 1
|
11天前
|
消息中间件 关系型数据库 Kafka
实时计算 Flink版产品使用合集之oracle cdc 抽取新增一张表 可以从savepoint恢复吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
5天前
|
资源调度 Oracle 关系型数据库
实时计算 Flink版产品使用合集之同步Oracle数据时,一张表产生了大量的连接数,如何处理
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
13天前
|
SQL Oracle 关系型数据库
Oracle 数据泵导出导入(映射表空间、Schema)
Oracle 数据泵导出导入(映射表空间、Schema)
|
13天前
|
SQL 存储 Oracle
Oracle中的Dual表:数据世界的“神奇小盒子”
【4月更文挑战第19天】Oracle的Dual表是一个虚拟表,仅含一行一列,常用于执行SQL函数、数据类型转换、测试语句和动态SQL。它是测试和便捷操作的工具,如获取当前日期(`SELECT SYSDATE FROM DUAL`)、数字转字符串(`SELECT TO_CHAR(12345) FROM DUAL`)。在存储过程、函数和触发器中也发挥重要作用,是数据库管理员的得力助手。
|
13天前
|
存储 Oracle 关系型数据库
Oracle系列之七:表的创建与管理
Oracle系列之七:表的创建与管理

推荐镜像

更多