Dump Oracle database into a textual SQL script

I am thinking on what mysqldump or pg_dump are doing. So, they dump an active database into a textual file containing the SQL queries, which reproduce this whole database. In MySQL and in PostgreSQL, this is the normal database dump format.

Does it exist on Oracle? How can it be done?

It is absolutely not a problem if it is not a standard Oracle thing. I need a solution to the problem. Exporting single tables is not enough, I am asking for the dump of a whole DB.

It is a reduced developer environment of a big project (with multi-million row DB), but still having hundreds of tables and a very complex structure. And, I want to play with it fast. While I am playing with it, I also need to be able to execute tricky modifications on that. On a binary dump I can't do that, on a textual I can.

Does it exist on Oracle?

Similar utility exists in Oracle environment called Data Pump.


How can it be done?

Use expdp command line tool to perform full database export.

Example:

expdp hr DIRECTORY=dpump_dir2 DUMPFILE=expfull.dmp FULL=YES NOLOGFILE=YES

Then can use impdp to import it in another database.

Example:

impdp hr DUMPFILE=dpump_dir1:expfull.dmp FULL=YES LOGFILE=dpump_dir2:full_imp.log

If you specify DUMPFILE parameter it writes a binary file called dump file. With the SQLFILE parameter(SQLFILE=/my/file/name.sql) you can get all of the SQL DDL that Import would have executed.

More on it depends upon the requirements.

Oracle Documentation explains more than I can here.

Overview of Oracle Data Pump

StackExchange Database Administrators Q#131726, answer score: 8