It is possible to export a table containing BLOB data without data in the blob column. Starting with 11g r2, REMAP_DATA can be used with DataPump export to export a table without blob data.
To use REMAP_DATA, you first need a function to change the data.
You can create the required function as follows. The function will be created in a package.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
create or replace package remap_lob as function null_lob(col blob) return blob; end remap_lob; / create or replace package body remap_lob as function null_lob(col blob) return blob is eblob blob; begin return null; end null_lob; end remap_lob; / |
We can then use this function with the REMAP_DATA feature during export.
In the example below, the blob column named blon_test of the test_lob table is modified and the expdp command is specified for export. The user to execute the expdp command must have execute permission for the function we have created above.
|
1 |
expdp user/password DIRECTORY=directory_name TABLES=schema_name.test_lob REMAP_DATA=schema_name.test_lob.blon_test:schema_name.remap_lob.null_lob |
