3

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.

Improve this question
5
  • 3
    No, you'd need to write your own extract scripts. You can get the DDL with the dbms_metadata package, but you'll need to generate your own insert statements. (Or find an automated dynamic example on the 'net somewhere). What's wrong with a binary dump file though?
    – Alex Poole
    Feb 10 '14 at 18:20
  • hi @AlexPoole i need to track code changes on git, i always export it from pl/sql developer but i need automation...
    – yet
    Feb 10 '14 at 18:23
  • Well, here's a list of reasons this isn't simple, and here's Tom Kyte's view; that has some attempts at generating the statements dynamically, but I'd be very careful about checking anything you find like this works for all your data *8-)
    – Alex Poole
    Feb 10 '14 at 18:31
  • oh i see... It seems very tricky and error-prone
    – yet
    Feb 10 '14 at 18:41
  • 2
    I'd also suggest this sounds backwards. You seem to want the schema and data in your database to be the 'master'. It would be simpler to keep your DDL and DML (presumably for initial base data) statements in files that you maintain outside the database, and check those in; and make changes to your DB using those files. Making ad hoc changes and then trying to track them afterwards sounds painful. But, just my opinion of course.
    – Alex Poole
    Feb 10 '14 at 18:43
2

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.

Improve this answer
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy