1

I want to create an SQL file from an Oracle DMP file.

With the command

"impdp [system] / [password] DUMPFILE = dampfilename.dmp SQLFILE = sqlfilename.sql logfile = logfilename.log full = Y show = y"

only the structure is in the sql file. When I look into the DMP file with a HEX editor, the data is included.

[system] / [password] are the same ones that were used with beo expdp.

I'm using release 11.2.0.1.0

wiegehtdasblos
  • 11
  • 1
  • 4
  • stand up a new oracle instance, even XE on Docker, shouldn't take long, then do an data pump IMPORT - once the db is up, you can extract the data in any fashion you need. But data pump DMP files are for data pump ONLY – thatjeffsmith Jul 07 '20 at 13:43
  • I now have the DMP file with me "impdp [system] / [password] DUMPFILE = dampfilename.dmp full = Y show = y" imported. How do I get all tables into a file that can be read with an editor (CSV, SQL, ..) from the database? – wiegehtdasblos Jul 09 '20 at 07:36
  • SQL Developer can help...for example connect to your database and look at the Tools > Database Export. Uncheck the DDL, check Data, check your format, tell it to go to separate files. It'll take awhile depending on large large your DMP was originally. – thatjeffsmith Jul 09 '20 at 09:39
  • That sounds great. My problem now is that I cannot log in with SQL Developer. I am getting the error ORA-12505. The default SID "XE" is probably wrong. – wiegehtdasblos Jul 13 '20 at 10:41
  • yeah, unless you called your db XE, it's not right. – thatjeffsmith Jul 13 '20 at 18:29

1 Answers1

1

The parameter SQLFILE for the Import datapump specifies a file into which all of the SQL DDL that Import would have executed, based on other parameters, is written.

If SQLFILE is specified, then the CONTENT parameter is ignored if it is set to either ALL or DATA_ONLY. That is because SQLFILE only will show DDLs included in the datapump file.

Putting SQLFILE does not exeucte anything in the target database where you are running the impdp, it will only write a file in the directory you have specified on the parameter directory.

Summary: Import datapump will not write any dml to the sqlfile

Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
  • which way is there to get the data in a DMP file, no matter if SQL, CSV, ..? – wiegehtdasblos Jul 07 '20 at 13:22
  • 1
    as far as I know, the only way to get the data is importing it. – Roberto Hernandez Jul 07 '20 at 13:24
  • now i have read the DMP-file into a personal-oracle-db. select * from [table-name] works. There are a large number of tables in the database. Is there a way to export all tables to a file (e.g. CSV, SQL, ..)? – wiegehtdasblos Jul 07 '20 at 15:09
  • you mean to export all ddls ? or export the data to csv files ? – Roberto Hernandez Jul 07 '20 at 15:12
  • both are helpful. What I'm looking for is something like expdp to csv or select * from all tables – wiegehtdasblos Jul 07 '20 at 15:31
  • you can export all the DDLs in several ways, using expdp and impdp sqlfile is a good way to get all ddls and dependencies. Exporting data to csv using Oracle you have two options: using sqlplus and spool ( but is manual and a lot of work ) or using an standard pl/sql procedure and utl_file to write the content of a table to the filesystem as csv or txt – Roberto Hernandez Jul 07 '20 at 16:00