Import Tables without Constraints from Schema Export

datapumpimpdporacleoracle-11g-r2

I am trying to do an import using data pump, but I am struggling to come up with the right options. The export was taken as a schema export. I am trying to import just the tables, with no other database objects.

I am currently using

INCLUDE=TABLE
TABLE_EXISTS_ACTION=REPLACE
REMAP_SCHEMA=
REMAP_TABLESPACE=

This is correctly importing the tables, but I am a little surprised to see that the import is also creating the constraints and triggers on the tables, or trying to at least. The triggers are failing because they include a schema name that does not exist in the database.

I thought that by using INCLUDE=TABLE only the tables would be included. Apparently that is not the case. I am unable to use EXCLUDE=CONSTRAINT or EXCLUDE=TRIGGER as I am already using INCLUDE to limit the import to just tables.

Any ideas on how I could structure the import to only import tables?

Best Answer

You are right when stating I thought that by using INCLUDE=TABLE only the tables would be included, but it includes all related objects too. This means that constraints and triggers will be created for you when you use INCLUDE=TABLE when importing.

Possible Solution

If you have the possibility to re-create the dump using the expdp command then you might want to just export the tables of the required schema and use the INCLUDE parameter.

INCLUDE

Purpose: Enables you to filter the metadata that is exported by specifying objects and object types for the current export mode. The specified objects and all their dependent objects are exported. Grants on these objects are also exported.

exp_include.par

This is basically the expdp parameter file.

FULL=N
...
SCHEMAS=<your_schema(s)>
INCLUDE=TABLE
...

This will create a dump of the required schema and contain only tables and dependant objects.

After you have generated the required *.dmp file, import using the EXCLUDE=... option.

EXCLUDE

Purpose: Enables you to filter the metadata that is imported by specifying objects and object types to exclude from the import job.

imp_exclude.par

This is the parameter file for the impdp.

FULL=Y
...
EXCLUDE=STATISTIC
EXCLUDE=CONSTRAINT
EXCLUDE=TRIGGER
...

This should result in the tables being imported correctly.

Reference Material

2 Data Pump Export (Oracle Docs)
3 Data Pump Import (Oracle Docs)

Related Solutions

Export table without BLOB column

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.

Oracle Data Pump Import imports no records

I suspect this may have something to do with you starting impdp as sysdba. Please see the note in http://docs.oracle.com/database/121/SUTIL/dp_export.htm#SUTIL824:

"Do not start Export as SYSDBA, except at the request of Oracle technical support. SYSDBA is used internally and has specialized functions; its behavior is not the same as for general users."

Try your command as system.