Oracle expdp 导出排除大对象数据

前言

有时候我们在搭建测试环境需要从生产库中导出数据来测试时，发现生产数据太大，数据库存放了大量图片等大对象数据，测试环境空间不够，但这些图片数据在测试时是可以不用的。那么我们如何在导出时排除这些图片等大对象数据呢
在expdp中提供对导出数据进行选择的参数有：exclude、include、query；
exclude和include是一对反义词，用来导出时选择排除（exclude)相关对象或者只导出选择（include)对象及其相关依赖对象。include和exclude操作的都是对象类型，所以对某张表的部分数据进行选择是不可行的，那么还剩下一个query参数可以期待下。
expdp -help看下QUERY注释如下：
QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:“WHERE department_id > 10”.
从注释中可以看到 query是用来导出一个表子集的谓词条件，相当于只能作用于 查询的where部分，没办法用于选择 select 列部分，所以query也是没办法实现不导出大对象（某一列）的需求的。

VIEWS_AS_TABLES

从上面看11g及之前是没有办法实现需求了，但12C及之后expdp引进了一个新的参数：VIEWS_AS_TABLES
expdp -help看下VIEWS_AS_TABLES注释如下：
Identifies one or more views to be exported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.
VIEWS_AS_TABLES 用于对将一个或多个视图数据作为表被导出，意思就是可以直接导出视图的数据了。
既然可以导出视图，那么就简单了，我们就可以将我们要导出的列创建为一个视图，然后导出这个视图查询的数据也就实现了只导出自己需要的数据的需求了，下面就来测试一把：
如下创建测试表：test含clob字段 content

-- drop table test;
create table test (id int,name varchar(128),content clob);
create index ind_name_test on test(name);
alter table test add constraint pk_id_test primary key(id);
insert into test select object_id,object_name,object_name||object_type from dba_objects where object_id>0;

--查看源表索引及约束
select index_name from dba_indexes t where t.owner='HR' and t.table_name='TEST';

INDEX_NAME
--------------------
SYS_IL0000079622C00003$$
IND_NAME_TEST
PK_ID_TEST
SQL> select CONSTRAINT_NAME  from dba_constraints t where t.owner='HR' and t.table_name='TEST';

CONSTRAINT_NAME
----------------------
PK_ID_TEST

源库创建视图：

create view v_test as select id,name from test;

源库导出视图：

cat expdp_view.par 
directory=dump_dir
dumpfile=exp_vtest1.dmp
logfile=exp_vtest1.log
VIEWS_AS_TABLES=HR.V_TEST    --导出指定视图

执行导出：

[oracle@og1 ~]$ expdp "' / as sysdba '" parfile=expdp_view.par
Export: Release 19.0.0.0.0 - Production on Mon Mar 21 10:33:57 2022
Version 19.12.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" parfile=expdp_view.par 
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "HR"."V_TEST"                               3.043 MB   74953 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/dump/exp_vtest1.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Mar 21 10:34:07 2022 elapsed 0 00:00:10

目标库执行导入：

cat impdp_view.par
directory=dump_dir
dumpfile=exp_vtest1.dmp
logfile=imp_vtest1.log
remap_table=v_test:test         --将导出的原视图名映射为原表名，如果不加该参数则导入的表名为v_test

执行导入：impdp “’ / as sysdba '” parfile=impdp_view.par

[oracle@og1 ~]$ impdp "' / as sysdba '" parfile=impdp_view.par  

Import: Release 19.0.0.0.0 - Production on Mon Mar 21 13:19:00 2022
Version 19.12.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" parfile=impdp_view.par 
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "HR"."TEST"                                 3.043 MB   74953 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Mon Mar 21 13:19:03 2022 elapsed 0 00:00:02

导入后的test 大字段列不存在了，如需定义则将其添加进来,另外导入后的表的索引、约束也是不存在的，需要再单独导入或者手动创建。
alter table test add content clob;

SQL> select object_name,object_type from dba_objects where owner='HR' and object_name like '%TEST';

OBJECT_NAME     OBJECT_TYPE
--------------- -----------------------
TEST            TABLE
SQL> select count(*) from test;

  COUNT(*)
----------
     74953
SQL> desc test;
 Name               Null?    Type
 ------------------ -------- -----------------------------------
 ID                          NUMBER(38)
 NAME                        VARCHAR2(128)

SQL> alter table test add content clob;

Table altered.

SQL>  desc test;
 Name                                                                                                              Null?    Type
 ---------------------- -------- -----------------
 ID                               NUMBER(38)
 NAME                             VARCHAR2(128)
 CONTENT                          CLOB
-- 原test表创建的主键及索引IND_NAME_TEST都不存在了
SQL> select index_name from dba_indexes t where t.owner='HR' and t.table_name='TEST';

INDEX_NAME
--------------------------------------------------------------------------------
SYS_IL0000079707C00003$$    --lob字段索引随lob对象创建而自动创建

SQL> select CONSTRAINT_NAME  from dba_constraints t where t.owner='HR' and t.table_name='TEST';

no rows selected

结论

12C以上版本后expdp的新特性 VIEWS_AS_TABLES 参数是可以实现将一张表的部分列选择导出的，不过导入后需要注意新表的索引、约束是不存在的，需要再单独导入或者创建。如果需要缺失的列的定义也是需要手动添加的。