Home / server / Questions / 320764
In Process
niXar
Asked: 2011-10-13 02:56:01 +0800 CST

Is there a way to dump an Oracle db as SQL statements, à la pg_dump?

  • 772

I'm very new to Oracle. It looks like all the Oracle DBA use binary only dumps. This sometimes cause problems, and seems rather useless to me (performance gain is bound to be negligible), but what do I know.

  1. Is there a good reason that escapes me?

  2. Is there a tool like pg_dump for Postgres that can generate SQL statements from a database?

One of the application could be moving from one version to another, or converting the data to another DB.

oracle postgresql

2 Answers

  1. I am not aware of any tool provided by Oracle that does what you ask. While I have in the past used DbVisualizer to export smaller tables/schemas from oracle (DDL and and insert statements) it isn't quite the same as pg_dump.

    However, if you are planing on "converting the data to another DB" where the other DB is postgres then take a look at ora2pg.

    • 3

  2. You can use datapump for this. Use expdp with option content=metadat_only, this creates a export with no data. You can use an export file containing data if available, but if you need to create one just for this writing the data is needless. Use impdp with option sqlfile=file_name.sql. This writes all DDL to file_name.sql.

    See this link for details

    • -1