Export table without BLOB column

Question

I have a big table with BLOB objects and some references from other tables to this one. I prepare dev and test schema for my app by making expdp (data pump) from production, then anonymizing data on dev/test host. The problem is that this table with the BLOBs is so large, that copying dump file from production to test/dev and then importing it takes ages...

Is there any solution to export SCHEMA without some columns in tables (maybe some filter which replaces all BLOBs with empty BLOBs or NULLs)?

I consider also making a "just BLOB schema" and link it to the production schema, and not export it at all, but this could cause performance issues.

Answer 1

You could do that in Oracle 11gR2 with expdp and the REMAP_DATA option.

1. Create a function in a package that takes a blob as argument, and returns null (or an empty blob perhaps).

2. Call expdp as usual, adding:

   REMAP_DATA=SCHEMA.TAB.BLOB_COLUM:SCHEMA.PACKAGE.YOUR_FUNCTION
   

Short example (schema: mat):

create or replace 
package remap as 
  function null_lob(col blob) return blob;
end remap;
/

create or replace
package body remap as
  function null_lob(col blob)
    return blob is
    eblob blob;
  begin
    return null;
  end null_lob;
end remap;
/

create table foo(a number, b blob);
-- insert a line with a 10M blob, commit

select a, length(b) from foo;

     A  LENGTH(B)
---------- ----------
     1   10485760

Plain export:

$ expdp mat/*** DIRECTORY=EXP_DIR TABLES=MAT.FOO 
...
Total estimation using BLOCKS method: 11.06 MB
...
. . exported "MAT"."FOO"                                 10.00 MB       1 rows
...

Dump file is ~11M.

With the data remap:

$ expdp mat/*** DIRECTORY=EXP_DIR TABLES=MAT.FOO \
                REMAP_DATA=MAT.FOO.B:MAT.remap.null_lob
...
Total estimation using BLOCKS method: 11.06 MB
...
. . exported "MAT"."FOO"                                 5.398 KB       1 rows
...

Dump file is ~100k, importing that dump does indeed import a null in the column.

---

Another option would be to use an external table to do the load/unload, with the ORACLE_DATAPUMP driver (I think this was introduced in 10g, see external table concepts).

For the unload, use something like:

create table foo_unload
  organization external (
    type oracle_datapump
    default directory exp_dir
    location ('foo.dmp')
  )
as select a, empty_blob() b from foo;

(You can drop the external table right after creating it, that doesn't - by default anyway - delete the dump file.)

Then move the foo.dmp file to your target server & directory, and create the external table there:

create table foo_load (a number, b blob)
  organization external
    type oracle_datapump
    default directory exp_dir
    location ('foo.dmp')
  );

And finally load your target table (CTAS or truncate ...; insert /*+ append */ ... for instance):

create table foo as select * from foo_load;

Drop the external loader table once that's done.