exporting oracle tables on command line as sql files

Posted on

Question :

I use exp and expdp to export database tables on command line but it generates binary .dmp files. I want to export/import tables as .sql* files on command line like pl/sql developer app can do. Is there a specific command for this or extra parameters for exp/expdp?

*sql files must contain table creation scripts and insert statements.

Thanks.

Answer :

You can generate DDL statements using imp/impdp utilities:

imp user/passwd file=export.dmp show=y log=export.sql 2> /dev/null
impdp user/passwd file=DIR:export.expdp sqlfile=DIR:export2.sql

But this method will generate just schema DDL. INSERT statements will not be generated.

I have recently had almost the same need, so I have written a simple script to create insert statements. It is located at github. However, the script does not create the table creation statements. You should use dbms_metadata for this.

Leave a Reply

Your email address will not be published. Required fields are marked *