Import dump with SQLFILE parameter not returning the data inside the table

Question

I am trying to import the dump file to .sql file using SQLFILE parameter.

I used the command "impdp username/password DIRECTORY=dir DUMPFILE=sample.dmp SQLFILE=sample.sql LOGFILE=sample.log"

I expected this to return a sql file with contents inside the table. But it created a sql file with only DDL queries.

For export I used, "expdp username/password DIRECTORY=dir DUMPFILE=sample.dmp LOGFILE=sample.log FULL=y"

Dump file size is 130 GB. So, I believe the dump has been exported correctly.

Am I missing something in the import command? Is there any other parameter should I use to get the contents?

Thanks in advance!

Alex Poole · Accepted Answer · 2014-07-01 15:17:20Z

2

Your expectation was wrong, I'm afraid. You're asking it to do something it isn't designed for.

The documentation for SQLFILE says:

Purpose

Specifies a file into which all of the SQL DDL that Import would have executed, based on other parameters, is written.

So it will only ever contain DDL.

There isn't a mechanism to turn a .dmp file into a .sql containing insert statements. If you need to put the data into a table, just use the native import.

Individual insert statements - if you could generate them, which SQL Developer will do as a separate task unrelated to your data pump export - would be slower, would have problems with LOBs, and would have to be careful about the order they were run unless integrity constraints were disabled. Data pump takes care of all of that for you.

answered Jul 1, 2014 at 15:17

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import dump with SQLFILE parameter not returning the data inside the table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related