oracle数据泵备份部分数据一例

一、需求
备份db库ABCD用户数据作为归档，排除表打头为如下的：
如ZWZ_*、TMP_*、...

二、分析
注意，这里不能使用like多个对象的方式了。改用regexp_like。^()，代表右模糊查询。
select table_name from dba_tables where owner='ABCD' and regexp_like(object_name,'^(ZWZ_|TMP_|ZJDZ|ZJFP|DZYH|T_CO|GL_D|QL_Y|XQW0|YGQD|SQL_|ZJJS|ZJPZ|TM_T|ZJ_T|TX_T|ZJLS|ZJRQ|TEMP|ZXB_|ZJHK|MLOG|SJHM|HZCL|ZJZF|GL_T|TMP|XQW_|ZJCY|ZJYL|PLAN|ZJCT|ZJJY|ZJRJ|ZJWL|SXJG|T_TE|FJSP|GL_F|GL_Y|JCSJ|JYFS|YSJX|YYB|BK_O|YGB_|KU$_|SJQY|TEST|WFCO|GNXB|SJYJ|TB_Z|ZJ_J|T_TA|ZZJJ|WDSR|XQW2|XQW3|SHYG|JYGM|T_ER|YYBM|LCZ_|ZJTB|ZJPD|SCQY|ZJTJ|ZJCK|ZJCL|ZJGP|ZJRB|ZJYJ|TDJG|GNDM|ZDJK|HCY_|ERP_|ABCD|YYSJ|TAB_|XYYJ|CP_C|ZZJT|ZJZZ|ZJTM|TB_T|PSW_|GJYJ|TJDY|DSJP|COMM|ZD_G|NSRD|T|QDGL|JGYY|XYYB|ZJBC|ZJQY|QY_T|HWK_|XQW4|SPJL|YYSC|JGSJ|T_YY|ZJJK|CW_T|CQJG|SXHK|YYGD|NMYG|ZJER|JGB_|YWGL|SGWD|YZTD|PXYY|SYS_|YTGH|LXY_)');
以上表需要排除。

字段超长出错，拆分为2：
1、
select count(*) from dba_tables where owner='ABCD' and regexp_like(table_name,'^(ZWZ_|TMP_|ZJDZ|ZJFP|DZYH|T_CO|GL_D|QL_Y|XQW0|YGQD|SQL_|ZJJS|ZJPZ|TM_T|ZJ_T|TX_T|ZJLS|ZJRQ|TEMP|ZXB_|ZJHK|MLOG|SJHM|HZCL|ZJZF|GL_T|TMP|XQW_|ZJCY|ZJYL|PLAN|ZJCT|ZJJY|ZJRJ|ZJWL|SXJG|T_TE|FJSP|GL_F|GL_Y|JCSJ|JYFS|YSJX|YYB|BK_O|YGB_|KU$_|SJQY|TEST|WFCO|GNXB|SJYJ|TB_Z|ZJ_J|T_TA|ZZJJ|WDSR|XQW2|XQW3|SHYG|JYGM|T_ER|YYBM|LCZ_)');
778

2、
select count(*) from dba_tables where owner='ABCD' and  regexp_like(table_name,'^(ZJTB|ZJPD|SCQY|ZJTJ|ZJCK|ZJCL|ZJGP|ZJRB|ZJYJ|TDJG|GNDM|ZDJK|HCY_|ERP_|ABCD|YYSJ|TAB_|XYYJ|CP_C|ZZJT|ZJZZ|ZJTM|TB_T|PSW_|GJYJ|TJDY|DSJP|COMM|ZD_G|NSRD|T|QDGL|JGYY|XYYB|ZJBC|ZJQY|QY_T|HWK_|XQW4|SPJL|YYSC|JGSJ|T_YY|ZJJK|CW_T|CQJG|SXHK|YYGD|NMYG|ZJER|JGB_|YWGL|SGWD|YZTD|PXYY|SYS_|YTGH|LXY_)');
628

共1406张表。

select count(*) from dba_tables where owner='ABCD';
1690
去掉1406，剩余284张表需要备份。
并且要备份表之外的对象。

执行三个步骤：
备份需要的表；
备份表之外的对象；
用exp方式导出表结构和其他对象，不导出数据，作为补充。

三、实施
以sysdba用户实施，取出所有ABCD表名，取出要排除的所有表名，相减获得需要的表名。

create table a1 as select table_name from dba_tables where owner='ABCD';

create table b1 as
select table_name from dba_tables where owner='ABCD' and regexp_like(table_name,'^(ZWZ_|TMP_|ZJDZ|ZJFP|DZYH|T_CO|GL_D|QL_Y|XQW0|YGQD|SQL_|ZJJS|ZJPZ|TM_T|ZJ_T|TX_T|ZJLS|ZJRQ|TEMP|ZXB_|ZJHK|MLOG|SJHM|HZCL|ZJZF|GL_T|TMP|XQW_|ZJCY|ZJYL|PLAN|ZJCT|ZJJY|ZJRJ|ZJWL|SXJG|T_TE|FJSP|GL_F|GL_Y|JCSJ|JYFS|YSJX|YYB|BK_O|YGB_|KU$_|SJQY|TEST|WFCO|GNXB|SJYJ|TB_Z|ZJ_J|T_TA|ZZJJ|WDSR|XQW2|XQW3|SHYG|JYGM|T_ER|YYBM|LCZ_)');

insert into b1 
select table_name from dba_tables where owner='ABCD' and regexp_like(table_name,'^(ZJTB|ZJPD|SCQY|ZJTJ|ZJCK|ZJCL|ZJGP|ZJRB|ZJYJ|TDJG|GNDM|ZDJK|HCY_|ERP_|ABCD|YYSJ|TAB_|XYYJ|CP_C|ZZJT|ZJZZ|ZJTM|TB_T|PSW_|GJYJ|TJDY|DSJP|COMM|ZD_G|NSRD|T|QDGL|JGYY|XYYB|ZJBC|ZJQY|QY_T|HWK_|XQW4|SPJL|YYSC|JGSJ|T_YY|ZJJK|CW_T|CQJG|SXHK|YYGD|NMYG|ZJER|JGB_|YWGL|SGWD|YZTD|PXYY|SYS_|YTGH|LXY_)');
628 rows created.

delete from a1 where a1.table_name in (select table_name from b1);
1306 rows deleted.

SQL> select count(*) from a1;
  COUNT(*)
----------
       384
实际导出384个表。

备份需要的表：
parfile
：
schemas=ABCD
include=TABLE:"in (select table_name from sys.a1)"
dumpfile=ABCD0402_%U.dmp
filesize=20G
directory=DPDATA1
logfile=ABCDpdp1.log
parallel=8
cluster=n
compression=all
job_name=ABCDmytbs

备份表之外的对象：
parfile
：
schemas=ABCD
exclude=TABLE
dumpfile=ABCD0403_%U.dmp
filesize=20G
directory=DPDATA1
logfile=ABCDpdp2.log
parallel=8
cluster=n
compression=all
job_name=ABCDnotbs

用exp方式导出表结构和其他对象，不导出数据，作为补充。
：
nohup exp ABCD/Yy9l2013 file=objects_with_norows.dmp log=objects_with_norows.log rows=n &

三、验证数据
测试库执行。
测试一，导入纯ABCD表
nohup impdp \'/ as sysdba\' dumpfile=ABCD0402_%U.dmp logfile=impdp-ABCD0402tbs.log parallel=8 directory=DPDATA remap_tablespace=ABCDBIGDATA:ABCDDATA,ABCDBIGIDX:ABCDDATA,...,FDA_FLASHBACK:ABCDDATA job_name=impdptbs >/tmp/out.01 &
原库与测试库，抽查了部分表的数量正确。

此时测试库ABCD的对象情况：
SQL> select object_type,count(*) from dba_objects where owner='ABCD' group by object_type order by 1;

OBJECT_TYPE               COUNT(*)
----------------------- ----------
INDEX                          367
INDEX PARTITION               2779
LOB                              9
TABLE                          384
TABLE PARTITION               2779
TRIGGER                          1

6 rows selected.

测试二，在测试一的基础上导入ABCD的所有非表对象。
nohup impdp \'/ as sysdba\' dumpfile=ABCD0403_%U.dmp logfile=impdp-ABCD0403obj.log parallel=4 directory=DPDATA remap_tablespace=ABCDBIGDATA:ABCDDATA,ABCDBIGIDX:ABCDDATA,...,FDA_FLASHBACK:ABCDDATA job_name=impdpobj >/tmp/out.02 &

此时测试库ABCD的对象情况：
SQL> select object_type,count(*) from dba_objects where owner='ABCD' group by object_type order by 1;
OBJECT_TYPE               COUNT(*)
----------------------- ----------
DATABASE LINK                    7
FUNCTION                        25
INDEX                          367
INDEX PARTITION               2779
LOB                              9
MATERIALIZED VIEW                1
PACKAGE                          2
PACKAGE BODY                     1
PROCEDURE                      220
SEQUENCE                       195
SYNONYM                        130
TABLE                          384
TABLE PARTITION               2779
TRIGGER                          1
TYPE                           369
VIEW                           459

16 rows selected.

测试三，导入结构表和对象到另外一个用户ABCDNEW，用于比较。
nohup imp \'/ as sysdba\' file=objects_with_norows.dmp log=imp_norows.log fromuser=ABCD touser=ABCDNEW &

此时测试库ABCDNEW的对象情况：
SQL> select object_type,count(*) from dba_objects where owner='ABCDNEW' group by object_type order by 1;

OBJECT_TYPE               COUNT(*)
----------------------- ----------
FUNCTION                        25
INDEX                         1437
LOB                              9
PACKAGE                          2
PACKAGE BODY                     1
PROCEDURE                      220
SEQUENCE                       195
SYNONYM                        130
TABLE                         1679
TABLE PARTITION                153
TRIGGER                          1
VIEW                           459

12 rows selected.

对比测试二和测试三的情况，表数量不一致忽略掉，其他对象上，测试二更全面，多了用户dblink和物化视图等。
测试二的结果可作为有数据需求的副本使用，测试三的结果可作为无数据的纯结构的副本使用。