10

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.

Improve this question
0

4 Answers 4

Reset to default
5

There is no such thing in the database itself.

You can perform something similar in SQL Developer, with the Database Export tool.

How to Export Data using SQL Developer

With the Tools / Database Export tool, you can specify the objects you want to export, then SQL Developer generates an SQL file for recreating them, with the data as insert statements.

Improve this answer
3
  • 1
    This is okay for one-at-a-time, interactive use, but not useful for scripting.
    – Andrew Schulman
    Mar 3, 2017 at 17:57
  • @Andrew Schulman Because such thing should not be scripted. We have Data Pump for moving data, and that does this more effectively.
    – Balazs Papp
    Mar 3, 2017 at 21:30
  • 1
    I have a need to script it. If Data Pump is the right tool to do that, then that would be a useful answer.
    – Andrew Schulman
    Mar 7, 2017 at 14:31
8

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

Improve this answer
1
  • Oracle Data Pump is not a valid answer to the question "into a textual SQL script", because it creates a file which no other system can read.
    – Holger Jakobs
    Jan 13, 2021 at 16:00
1

As the exact requirements are vague, I'll post this: You could also write the data you want to external tables. This would give you the data in (for example) CSV format which you could use to import using another database's import functionality.

Be aware that just getting the data as DDL/DML statements might not be enough to do what I suspect you're trying to do. There are differences in data types, lengths, formats and more which could cause problems if you're migrating from one instance type to another.

Improve this answer
1
  • The question is not at all vague. It's just "How to dump a database to a text file - including CREATE TABLE and INSERT commands" What is vague about this. Using a decent DBMS the answer would be easy. Example PostgreSQL: pg_dump > file.sql (if the default values for choosing server, user, database and other options are fine). This file can be read, adapted to the features of different DBMS and read like psql < file.sql to create the whole content on a different system.
    – Holger Jakobs
    Jan 13, 2021 at 16:04
-1

In some case tools like Flyway or Liquebase can be helpful for you, i think. Seems they can make dump in SQL, XML and maybe something else. This tools aren't very easy to use, but looks much more flexible then SQL Developer. For example if you need to filter data before uploading.

Improve this answer
1
  • I don't disagree with what's said, but to me this is not helpful as the reason for looking up this question in the first place is to get a dmp into a format that liquibase can load.
    – xenoterracide
    May 18, 2018 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.