Datapump sqlfile Parameter in Oracle 19c - orakldba

Datapump sqlfile Parameter in Oracle 19c : During this article we are going to see Datapump sqlfile Parameter in Oracle 19c and the way we are able to use sqlfile parameter in Oracle database.

It will be used, only with IMPDP. This sqlfile parameter helps you to generate the DDLs from a dumpfile. Suppose i’ve got dump file of a schema/user SCOTT.dmp. If you would like the DDL of the schema/user, then use SQLFILE with IMPDP command as below.

This will not do the actual import. It’ll just dump the metadata (DDL) of the schema/user within the specified .sql file.

For Generating DDL of a schema/user :

For Generating DDL of a schema/user Syntax :-

impdp directory=directory_name dumpfile=dumpfile_name sqlfile=sqlfile_name schemas=schema_name

Step-1 : For Generating DDL of a schema/user from a dumpfile :

[oracle@node-1 ~]$
[oracle@node-1 ~]$ impdp directory=exp_dir dumpfile=SCOTT.dmp sqlfile=SCOTT.sql schemas=scott

Import: Release 19.0.0.0.0 - Production on Tue Feb 23 20:29:17 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_SCHEMA_01":  /******** AS SYSDBA directory=exp_dir dumpfile=SCOTT.dmp sqlfile=SCOTT.sql schemas=scott
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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 "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Tue Feb 23 20:29:40 2021 elapsed 0 00:00:10

[oracle@node-1 ~]$ cd /u01/backup/
[oracle@node-1 backup]$
[oracle@node-1 backup]$ ls -ltrh
total 452K
-rw-r-----. 1 oracle oinstall 432K Feb 23 20:28 SCOTT.dmp
-rw-r--r--. 1 oracle oinstall 1.7K Feb 23 20:28 SCOTT.log
-rw-r--r--. 1 oracle oinstall 4.2K Feb 23 20:29 SCOTT.sql
-rw-r--r--. 1 oracle oinstall 1.2K Feb 23 20:29 import.log
[oracle@node-1 backup]$

Step- 2 Now check the sqlfile (SCOTT.sql) :

[oracle@node-1 backup]$ cat SCOTT.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
 CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:D37284221FD3DB1B6E70C6C5A01D429827F177BC081D88487935615D3A09;T:3670AFF53AF8485227C98791845516767EDC8BBAF3D56AC12531CB70DC6D683D43E657DC7471D88B162302F1BE3E0A3E12BC8E1F620083B64497A1DD3CFA5538419BC3C046D654E7EC4487D02BF93D2A'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "SCOTT";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "CONNECT" TO "SCOTT";
 GRANT "RESOURCE" TO "SCOTT";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "SCOTT" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT SCOTT

BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'ORCL', inst_scn=>'2544845');
COMMIT;
END;
/
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT SYS
CREATE TABLE "SCOTT"."BONUS"
   (    "ENAME" VARCHAR2(10 BYTE),
        "JOB" VARCHAR2(9 BYTE),
        "SAL" NUMBER,
        "COMM" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10 BYTE),
        "JOB" VARCHAR2(9 BYTE),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "SCOTT"."SALGRADE"
   (    "GRADE" NUMBER,
        "LOSAL" NUMBER,
        "HISAL" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "SCOTT"."DEPT"
   (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14 BYTE),
        "LOC" VARCHAR2(13 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;
-- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
-- new object type path: SCHEMA_EXPORT/STATISTICS/MARKER
-- fixup virtual columns...
-- done fixup virtual columns
[oracle@node-1 backup]$

For Generating DDL of a table from a dumpfile :

If you would liketo get the DDL of the table, then use SQLFILE with IMPDP command as below.

This will not do the actual import. It’ll just dump the metadata (DDL) of the table within the specified .sql file.

For Generating DDL of a table Syntax :-

impdp directory=directory_name dumpfile=dumpfile_name sqlfile=sqlfile_name tables=table_name

Step- 1 For Generating DDL of a table :

[oracle@node-1 backup]$
[oracle@node-1 backup]$ impdp directory=exp_dir dumpfile=SCOTT.dmp sqlfile=EMP_table.sql tables=SCOTT.EMP

Import: Release 19.0.0.0.0 - Production on Tue Feb 23 20:43:40 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_TABLE_01":  /******** AS SYSDBA directory=exp_dir dumpfile=SCOTT.dmp sqlfile=EMP_table.sql tables=SCOTT.EMP
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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 "SYS"."SYS_SQL_FILE_TABLE_01" successfully completed at Tue Feb 23 20:44:02 2021 elapsed 0 00:00:08

[oracle@node-1 backup]$
[oracle@node-1 backup]$ ls -ltrh
total 456K
-rw-r-----. 1 oracle oinstall 432K Feb 23 20:28 SCOTT.dmp
-rw-r--r--. 1 oracle oinstall 1.7K Feb 23 20:28 SCOTT.log
-rw-r--r--. 1 oracle oinstall 4.2K Feb 23 20:29 SCOTT.sql
-rw-r--r--. 1 oracle oinstall 1.8K Feb 23 20:44 EMP_table.sql
-rw-r--r--. 1 oracle oinstall  961 Feb 23 20:44 import.log
[oracle@node-1 backup]$

Step- 2 Now check the sqlfile (SCOTT.sql) :

[oracle@node-1 backup]$ cat EMP_table.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10 BYTE),
        "JOB" VARCHAR2(9 BYTE),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;
-- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
-- new object type path: SCHEMA_EXPORT/STATISTICS/MARKER
[oracle@node-1 backup]$

I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.

For Generating DDL of a schema/user :

For Generating DDL of a table from a dumpfile :

Leave a Comment Cancel reply