Excluding LOB Columns Data in Data Pump

 We intend to create a dump from a table in which some columns contain LOB data. However, due to the large volume of LOB data in this table — and for reasons such as higher speed, better security, or reduced space allocation — we want to prevent this data from being included in the dump file, or at least prevent it from being restored during the import process.

What is the solution?

Excluding LOB column data during the export operation in Oracle 12c can be easily controlled. This can be done by creating a view and using the VIEWS_AS_TABLES parameter. However, if the dump file already contains LOB data, this method cannot be used during import. In such cases, the REMAP_DATA parameter is the solution.

Previously, we explained that the REMAP_DATA parameter can be used for data masking and data manipulation during export and import operations. In this article, we will see how the same parameter can be used to exclude LOB column data during both export and import processes.

Let’s look at the following example.

Example

We want to exclude the data stored in the ADDRESS and RESUME columns of the PERSON table during expdp.

SQL> desc person

Name           Type       

——-  ————————

ID              NUMBER     

NAME       VARCHAR2(10)

ADDRESS   CLOB       

RESUME    CLOB    
   

SQL> select * from person;

ID    NAME       ADDRESS      RESUME

—- ———- ———-  ———–

1      usef      LOB_DATA    LOB_DATA

To achieve this, we create a package that returns NULL for each field in these two columns:

SQL> create or replace package without_CLOB as

  2   function null_clob (soton_clob in clob) return clob;

  3   end;

  4  /

Package created


SQL> create or replace package body without_CLOB as

  2   function null_clob (soton_clob in clob) return clob

  3   is

  4    nulllll   clob := empty_clob ();

  5  begin

  6      return  nulllll;

  7   end;

  8  end;

  9  /

Package body created

After creating the WITHOUT_CLOB package, we generate a dump from the PERSON table using the REMAP_DATA parameter:

[oracle@ol7 ~]$ expdp directory=tk dumpfile=dmp_without_LOB tables=usef.person REMAP_DATA=usef.person.ADDRESS:usef.without_CLOB.null_clob REMAP_DATA=usef.person.RESUME:usef.without_CLOB.null_clob

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Starting “SYS”.”SYS_EXPORT_TABLE_01″:  sys/******** AS SYSDBA directory=tk dumpfile=dmp_without_LOB tables=usef.person REMAP_DATA=usef.person.ADDRESS:usef.without_CLOB.null_clob REMAP_DATA=usef.person.RESUME:usef.without_CLOB.null_clob

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

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

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported “USEF”.”PERSON”                             6.398 KB       1 rows

Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

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

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

  /home/oracle/dmp_without_LOB.dmp

Job “SYS”.”SYS_EXPORT_TABLE_01″ successfully completed at Mon Jul 6 07:38:24 2020 elapsed 0 00:00:42

The export completes successfully, and the dump file is created.

Now, when we import the table again:

[oracle@ol7 ~]$ impdp directory=tk dumpfile=dmp_without_LOB tables=usef.person remap_table=person:person2

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported “USEF”.”PERSON2″                            6.398 KB       1 rows


SQL> select ADDRESS from usef.person2;

No Rows Selected.


SQL> select * from usef.person2;

        ID NAME       ADDRESS    RESUME

———- ———- ———- ———-

         1 usef

As shown, the ADDRESS and RESUME columns are empty.

Excluding LOB columns during the impdp operation is also possible.

Written by Vahid Yousefzadeh

I have been a DBA since 2011 and I work with Oracle technology. Linkdin: linkedin.com/in/vahidusefzadeh telegram channel ID:@oracledb vahidusefzadeh@gmail.com