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.