First of all you need to create a directory on the database.
Create or Replace Directory IMP_DIR as 'Path\Path\';
Ensure the user that will import the file has the privilige to Read & Write on the directory (In case you are not using SYS)
GRANT Read, Write ON DIRECTORY IMP_DIR TO user;
Then, include the directory parameter within the import statement
impdp 'sys AS SYSDBA' file=dump.dmp full=yes log=implog.txt directory=IMP_DIR
You could do that in Oracle 11gR2 with expdp
and the REMAP_DATA
option.
- Create a function in a package that takes a blob as argument, and returns
null
(or an empty blob perhaps).
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.
Best Answer
Another option would be to do a datapump export of a view that has only the columns and data that you want. If you want a column to be null have the view make the column null. Then when you do the import you are only importing the data that you want.
expdp VIEWS_AS_TABLES