0

I am trying to generate DDL statements from the data in a dump file, by importing the dump file using impdp.

This is my export log:

;;; 
Export: Release 12.2.0.1.0 - Production on Thu Dec 12 01:56:20 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SYSTEM"."AM_USERAUTHSTATE"                 543.8 MB 4607788 rows
. . exported "SYSTEM"."AM_OBJECT"                        425.1 MB 2500200 rows
. . exported "SYSTEM"."AM_AUDIT_A"                       8.707 MB   15663 rows
. . exported "SYSTEM"."AM_ATTRIBUTE"                     112.3 KB     746 rows
. . exported "SYSTEM"."QRTZ_TRIGGERS"                    12.25 KB       3 rows
. . exported "SYSTEM"."QRTZ_JOB_DETAILS"                 10.10 KB       3 rows
. . exported "SYSTEM"."AM_ANCESTRY"                      8.453 KB      20 rows
. . exported "SYSTEM"."AM_AUDIT_ATTR_A"                  8.312 KB       1 rows
. . exported "SYSTEM"."QRTZ_SIMPLE_TRIGGERS"             7.617 KB       3 rows
. . exported "SYSTEM"."AM_RELATION2"                     7.468 KB       4 rows
. . exported "SYSTEM"."QRTZ_SCHEDULER_STATE"             6.539 KB       2 rows
. . exported "SYSTEM"."AM_VARS"                          6.015 KB       2 rows
. . exported "SYSTEM"."QRTZ_LOCKS"                       5.609 KB       4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/amdb/dpdump/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Dec 12 01:57:26 2019 elapsed 0 00:01:00

I try to run the following command:

impdp chinkai/chinkai directory=dpdir dumpfile=expdat.dmp sqlfile=ddl.sql

And this is my import log:

;;; 
Import: Release 12.2.0.1.0 - Production on Tue Jan 28 11:11:31 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "CHINKAI"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "CHINKAI"."SYS_SQL_FILE_FULL_01":  chinkai/******** directory=dpdir dumpfile=expdat.dmp sqlfile=ddl.sql 
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "CHINKAI"."SYS_SQL_FILE_FULL_01" successfully completed at Tue Jan 28 11:12:00 2020 elapsed 0 00:00:28

It seems like the data had not been imported, but I'm not sure why. I would like to import everything from the dump file into my database.

(I'm unable to do another expdp from the source database as it had already been deleted; this dump file is all I have to work with.)

  • I don't see any errors in impdp except you used parameter SQLFILE=ddl.sql that generates sql file instead of importing.Remove that part and run impdp.By the objects are in System schema? – Kumar Jan 28 at 6:04
  • @Kumar The true objective of my import is to generate DDL statements from the data in the dump file, so instead of having a dump file that is specific to one database, I have SQL statements that I can run on any database, not just Oracle. Removing the argument helped; now I can import, but I still would like to see DDL statements. – user10931326 Jan 28 at 9:32
  • You've said above that this question isn't about importing data into an Oracle database, it's about trying to generate DDL statements. Can you please re-write or resubmit your question with full details of what you're trying to achieve so nobody wastes their time on an XY problem? – Phil Sumner Jan 28 at 14:17
  • @PhilSumner Done. Thanks for pointing it out. – user10931326 Jan 29 at 1:01
  • 1
    @user10931326 If you want table ddl as well as data as insert statements try SQL Developer or third party IDE tools.(Impdp sqlfile option generates only metadata) – Kumar Jan 29 at 4:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.