oracle expdp导出慢,Oracle expdp 需要导出大量表的处理

一个项目中需要在oracle数据库某个用户下导出1000多个表，导入到测试库做数据分析测试。很少遇到需要导出这么多表的情况，通常都是按schema导出，或者整库导出。考虑到expdp中include参数可以附带查询语句，本次遇到的需要导出大量具体表的建议思路：

1) 在数据库中建一个表exptable，把需要导出表的表明插入到该表中；

2)利用expdp导出命令的include参数附带select语句查询exptable表，获得需要导出的表名；

3)开始导出。

过程记录如下：

1 在数据库中建立一个导出列表exptable

create table exptable (id number(10),tablename varchar2(20));

2 使用sql*load工具把需要导出的表的名称输入到exptable中：

2.1 把需要导出的表名另存为txt文本，id和表名之间用空格隔开，每行以逗号结尾；

2.2 创建ctl文件：

[oracle@jxsbwxdb1 ~]$ cat a.ctl

load data

infile ‘/home/oracle/exptable.txt‘

append into table jxsi.exptable

fields terminated by ‘,‘ trailing nullcols

(id,tablename)

2.3 使用命令sqlldr输入数据

sqlldr jxsi/****** control=a.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Feb 26 22:39:04 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Control File: a.ctl

Data File: /home/oracle/exptable.txt

Bad File: exptable.bad

Discard File: none specified

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array: 64 rows, maximum of 256000 bytes

Continuation: none specified

Path used: Conventional

Table JXSI.EXPTABLE, loaded from every logical record.

Insert option in effect for this table: APPEND

TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype

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

ID FIRST * , CHARACTER

TABLENAME NEXT * , CHARACTER

Table JXSI.EXPTABLE:

1056 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array: 33024 bytes(64 rows)

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 1056

Total logical records rejected: 0

Total logical records discarded: 0

Run began on Wed Feb 26 22:39:04 2020

Run ended on Wed Feb 26 22:39:04 2020

Elapsed time was: 00:00:00.13

CPU time was: 00:00:00.01

查询数据确认是否正确导入：

SQL> select count(*) from exptable;

COUNT(*)

----------

1056

3.expdp导出语句

expdp system/****** parfile=exptable.par

cat exptable.par

dumpfile=20200226_exptable_%U.dmp

logfile=20200226_exptable.log

schemas=jxsi

directory=DPDATA

PARALLEL=4

COMPRESSION=all

cluster=N

include=TABLE:"IN (select tablename from exptable where tablename is not null)"

或者：

expdp jxsi/****** directory=DPDATA dumpfile=20200226_exptable_%U.dmp schemas=jxsi include=table:\"in \(select tablename from exptable \)\" logfile=20200226_exptable.log cluster=N compression=all PARALLEL=4

Export: Release 11.2.0.4.0 - Production on Wed Feb 26 22:40:52 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

FLASHBACK automatically enabled to preserve database integrity.

Starting "JXSI"."SYS_EXPORT_SCHEMA_02": jxsi/******** directory=DPDATA dumpfile=20200226_exptable_%U.dmp schemas=jxsi include=table:"in (select tablename from exptable )" logfile=20200226_exptable.log cluster=N compression=all PARALLEL=4

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 220.7 GB

Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION

. . exported "JXSI"."AC43":"AC43_20" 1.890 GB 81857509 rows

. . exported "JXSI"."AC43":"AC43_10" 2.110 GB 94683422 rows

......

. . exported "JXSI"."P_RYQY" 4.921 KB 21 rows

. . exported "JXSI"."QTMXB" 10.15 KB 234 rows

Master table "JXSI"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded

******************************************************************************

Dump file set for JXSI.SYS_EXPORT_SCHEMA_02 is:

/tmpbak/DPDATA/20200226_exptable_01.dmp

/tmpbak/DPDATA/20200226_exptable_02.dmp

/tmpbak/DPDATA/20200226_exptable_03.dmp

/tmpbak/DPDATA/20200226_exptable_04.dmp

Job "JXSI"."SYS_EXPORT_SCHEMA_02" successfully completed at Wed Feb 26 23:22:23 2020 elapsed 0 00:41:30

正常导出。

Oracle expdp 需要导出大量表的处理

标签：name   建议   lock   arc   led   ber   bak   tis   copyright

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：https://www.cnblogs.com/caoyibin/p/12377278.html