oracle expdp exclude导入导出整个库 这里说“库”只是对开发人员将，绝大多数开发人员分不清什么是库，库说白了就是一些数据文件。开发人员说导入导出库，对DBA来讲其实是导入导出库中的数据。 废话短说，需求是这样滴：将某地的数据库数据拿来做开发测试库！ 我只问了开发人员哪些表不要，哪些表要，要的话需要多少数据量，为什么这么说呢？（1）我的方案：（所有应用数据对象都在oracle一个用户sa上）导出数据方案1、先导出sa用户，但是导出的时候通过exclude 排除哪些不要的表和数据量大的表 （大于5G）2、对那些大表，一个一个导出，并且通过query控制每个大表导出的数据量导入方案：1、先导入用户sa的dump文件2、在一个个导入各个大表dump文件（2）方案实施步骤：导出方案实施：1、创建导出对象专用用户（用哪个用户来导出sa用户的数据）2、创建dump文件存放目录3、排除哪些大表不导，确定大表导出的数据量4、开始导出sa用户数据5、开始导出表之分区表6、开始导出表之非分区表导入方案实施：1、创建表空间2、创建导入对象专用用户（用哪个用户来导入sa用户的数据，与导出用户一致）3、创建dump文件存放目录4、创建profile文件5、开始导入用户sa的dump文件6、逐个导入各个大表的dump文件注意：用户模式导入，一定要确保和导出的一直性：1、用户什么用户来导出需要导出的对象，就要用什么用户来导入需要导入的对象2、前面两个用户的默认表空间、默认临时表空间、默认profile一定要一一对应，当然如果表空间不想重新创建的话需要使用：REMAP_TABLESPACE指定使用哪个表空间3、导入库不能有需要导入的对象。当然，如果有的话需要使用REMAP_SCHEMA来指定将导出用户的所有对象导入到另一个用户中！导出方案实施：1/6分步阅读

1、创建导入导出对象专用用户（用哪个用户来导出导入sa用户的数据）

create user zxx identified by zxx default tablespaceREMOTE320DB;

grant connect,dba to zxx;

创建用户zxx并对zxx用户赋予连接和dba权限。这样zxx用户就有权限导出sa用户数据了。

注意：zxx用户使用的非表空间、默认临时表空间、默认profile文件，也就是说zxx用户的默认表空间为REMOTE320DB、默认临时表空间为TEMP、默认profile文件为DEFAULT。

那么导入用户zxx也必须使用这样的配置。

2/6

2、创建dump文件存放目录

SQL create directory dump_dir as /backup/zxx_dump;

SQL grant read,write on directory dump_dir to zxx;

第一句是创建dump目录为虚拟dump_dir，这个目录其实指向/backup/zxx_dump路径

第二句是赋予zxx用户对这个虚拟目录的读写权限

3/6

3、排除哪些大表不导，哪些大表导出的数据量

select * from ( select sum(bytes)/1024/1024/1024 sizem,segment_name from dba_segments where owner=SA and segment_type like TABLE% group by segment_name ) a where a.sizem 1

找出sa用户对象大小大于1G的表，经过和开发人员确认：

不需要的表有：

TXSBZT SPSBZT YJXX T_CLXX

单独导出部分数据的表：

CLWFXX CLXSGJ TPXZXX GJXX

最终确认expdp时需要过滤的表有：

TPXZXX TXSBZT CLWFXX SPSBZT YJXX CLXSGJ GJXX T_CLXX

4/6

4、开始导出sa用户数据

注意转义字符：

expdp zxx/zxx DIRECTORY=dump_dir DUMPFILE=sa.dmp LOGFILE=sa.log SCHEMAS=sa EXCLUDE=TABLE:\IN \(\TPXZXX\,\TXSBZT\,\CLWFXX\,\SPSBZT\, \YJXX\,\CLXSGJ\,\GJXX\,\T_CLXX\\)\ ;

exclude表示过滤哪些对象，TABLE:表示过滤哪些表

5/6

5、开始导出表之分区表

需要导出至少一个月的，采用parfile方便

$ cat exp_clxsgj.txt

userid=sa/wiscom

directory=dump_dir

dumpfile=clxsgj.dmp

LOGFILE=clxsgj.log

TABLES=CLXSGJ:CLXSGJ20150401,CLXSGJ:CLXSGJ20150402,CLXSGJ:CLXSGJ20150403,CLXSGJ:CLXSGJ20150404,CLXSGJ:CLXSGJ20150405,CLXSGJ:CLXSGJ20150406,CLXSGJ:CLXSGJ20150407,CLXSGJ:CLXSGJ20150408,CLXSGJ:CLXSGJ20150409,CLXSGJ:CLXSGJ20150410,CLXSGJ:CLXSGJ20150411,CLXSGJ:CLXSGJ20150412,CLXSGJ:CLXSGJ20150413,CLXSGJ:CLXSGJ20150414,CLXSGJ:CLXSGJ20150415,CLXSGJ:CLXSGJ20150416,CLXSGJ:CLXSGJ20150417,CLXSGJ:CLXSGJ20150418,CLXSGJ:CLXSGJ20150419,CLXSGJ:CLXSGJ20150420,CLXSGJ:CLXSGJ20150421,CLXSGJ:CLXSGJ20150422,CLXSGJ:CLXSGJ20150423,CLXSGJ:CLXSGJ20150424,CLXSGJ:CLXSGJ20150425,CLXSGJ:CLXSGJ20150426,CLXSGJ:CLXSGJ20150427,CLXSGJ:CLXSGJ20150428,CLXSGJ:CLXSGJ20150429,CLXSGJ:CLXSGJ20150430,CLXSGJ:CLXSGJ20150501,CLXSGJ:CLXSGJ20150502,CLXSGJ:CLXSGJ20150503,CLXSGJ:CLXSGJ20150504,CLXSGJ:CLXSGJ20150505,CLXSGJ:CLXSGJ20150506

$ expdp parfile=exp_clxsgj.txt

注意：tables不能用括号括起来

6/6

6、开始导出表之非分区表

expdp sa/wiscom directory=dump_dir dumpfile=clwfxx.dmp LOGFILE=clwfxx.log tables=clwfxx query=clwfxx:\ where jgsj between to_date\(\2014-04-01\,\yyyy-mm-dd\\) and to_date\(\2015-05-6\,\yyyy-mm-dd\\)\;

expdp sa/wiscom directory=dump_dir dumpfile=TPXZXX.dmp LOGFILE=TPXZXX.log tables=TPXZXX query=TPXZXX:\ where XZSJ between to_date\(\2014-04-01\,\yyyy-mm-dd\\) and to_date\(\2015-05-6\,\yyyy-mm-dd\\)\;

expdp sa/wiscom directory=dump_dir dumpfile=GJXX.dmp LOGFILE=GJXX.log tables=GJXX query=GJXX:\ where JGSJ between to_date\(\2014-04-01\,\yyyy-mm-dd\\) and to_date\(\2015-05-6\,\yyyy-mm-dd\\)\;

注意：

刚开始使用：

expdp zxx/zxx directory=dump_dir dumpfile=sa_clwfxx.dmp LOGFILE=sa_clwfxx.log TABLES=SA.CLWFXX QUERY=SA.CLWFXX:\WHERE jgsj \= TO_DATE\(\2015-04-20 00:00:00,\yyyy-mm-dd hh24:mi:ss\\) AND jgsj \ TO_DATE\(\2015-05-11 00:00:00\,\yyyy-mm-dd hh24:mi:ss\\)\ exclude=statistics parallel=2;

想导出SA.CLWFXX 表，但始终报错：

ORA-01861: literal does not match format string（字符串格式不匹配）

那么将query中的单引号换成双引号之后：

expdp zxx/zxx directory=dump_dir dumpfile=sa_clwfxx.dmp LOGFILE=sa_clwfxx.log TABLES=SA.CLWFXX QUERY=SA.CLWFXX:\WHERE jgsj \= TO_DATE\(\2015-04-20 00:00:00,\yyyy-mm-dd hh24:mi:ss\\) AND jgsj \ TO_DATE\(\2015-05-11 00:00:00\,\yyyy-mm-dd hh24:mi:ss\\)\ exclude=statistics parallel=2;

还是报错：ORA-01861: literal does not match format string

最后观察发现，在命令行中执行：

select sysdate from dual; 发现只有年月日

最后定位出来，query中有时间的话，最好不要加上时分秒。

导入方案实施：1/6

1、创建表空间

由于sa用户的默认表空间和sa用户的所有对象都在REMOTE320DB表空间上，所以对于impdp 用户模式必须要创建：导出专有用户zxx的默认表空间、sa用户的默认表空间和sa用户的所有对象都在REMOTE320DB表空间

create tablespace remote320db datafile /u01/app/oracle/oradata/orclone/data01.dbf size 30G;

alter tablespace remote320db add datafile /u01/app/oracle/oradata/orclone/data02.dbf size 30G;

2/6

2、创建导入对象专用用户（用哪个用户来导出导入sa用户的数据，与导出用户一致）

1、创建导入导出对象专用用户（用哪个用户来导出导入sa用户的数据）

create user zxx identified by zxx default tablespaceREMOTE320DB;

grant connect,dba to zxx;

和前面的导出用户对象一致，zxx用户的默认表空间为REMOTE320DB、默认临时表空间为TEMP、默认profile文件为DEFAULT。

3/6

3、创建dump文件存放目录

SQL create directory dump_dir as /zxx_software/dump;

SQL grant read,write on directory dump_dir to zxx;

创建完之后，将之前导出的4个*.dmp 文件拷贝到/zxx_software/dump目录，并对改用修改成oracle软件安装用户。

虽然导出的时候oracle软件安装用户是dbadmin,但是对于dump文件没有影响，测试数据的oracle软件安装用户是oracle，所以拷贝完所有文件之后，需要修改用户权限：

chown -Roracle:oinstall/zxx_software/dump

4/6

4、创建profile文件

create profile MONITORING_PROFILE limitfailed_login_attempts unlimited;

创建profile的语句最好从导出库中拷贝来创建

注意：如果没有创建sa用户的默认profile就会impdp失败。

5/6

5、开始导入用户sa的dump文件

impdp zxx/zxx directory=dump_dir dumpfile=sa.dmp schemas=sa logfile=sa.log;

导入的时候忽略 角色创建失败。因为角色创建失败对数据对象的导入没有影响！从下图中就可以看出！

注意：用户模式导入，导入库一定不能存在sa用户

6/6

6、逐个导入各个大表的dump文件

impdp sa/wiscom directory=dump_dir dumpfile=clxsgj.dmp LOGFILE=clxsgj.log tables=clxsgj

表导出用什么用户，导入就用什么用户

oracle