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
- If
SQLFILE
is specified, then theCONTENT
parameter is ignored if it is set to eitherALL
orDATA_ONLY
. - 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. - The
SQLFILE
parameter cannot be used in conjunction with theQUERY
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.