Tag: dumpfile

19c, Oracle

Create DDL from dump file

iamdbablog

We will see the Oracle 18c/19c Datapump- SQLFILE
This parameter is used in impdp operation. It will create sqlfile with DDL that could be executed in another database/schema to create the tables and indexes.
Datapump import does not perform an import of the contents of the dump file.  Rather, the sqlfile parameter create a script that contains all DDL from within the export dump file.

Restrictions

  1. If SQLFILE is specified, then the CONTENT parameter is ignored if it is set to either ALL or DATA_ONLY.
  2. To perform a Data Pump Import to a SQL file using Oracle Automatic Storage Management (Oracle ASM), the SQLFILE parameter that you specify must include a directory object that does not use the Oracle ASM + notation. That is, the SQL file must be written to a disk file, not into the Oracle ASM storage.
  3. The SQLFILE parameter cannot be used in conjunction with the QUERY parameter.

Example

impdp oracle/oracle DIRECTORY=TESTDIR DUMPFILE=test.dmp LOGFILE=test.log sqlfile=test_fulldb.sql

Note that passwords are not included in the SQL file. For example, if a CONNECT statement is part of the DDL that was executed, then it will be replaced by a comment with only the schema name shown.
Therefore, before you can execute the SQL file, you must edit it by removing the dashes indicating a comment and adding the password for the schema.