Generate DDL script of Schema with EXPDP/IMPDP in Oracle

Get DDL of all objects in one script of Schema in Oracle

  1. EXPDP the schema with METADATA_ONLY option
C:\Users\ORACLE>expdp directory=dbbackup dumpfile=hr_metadata.dmp content=metadata_only

Export: Release 18.0.0.0.0 - Production on Wed Feb 2 22:15:37 2022
Version 18.4.0.0.0

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

Username: hr@pdb1
Password:

Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_SCHEMA_01":  hr/********@pdb1 directory=dbbackup dumpfile=hr_metadata.dmp content=metadata_only
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/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/TABLESPACE_QUOTA
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/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/JOB
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
  C:\D\BOOKMARK\HR_METADATA.DMP
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Feb 2 22:16:31 2022 elapsed 0 00:00:48

2. Use IMPDP for Converting the dump file to the SQL file.

C:\Users\ORACLE>impdp directory=dbbackup dumpfile=hr_metadata.dmp sqlfile=hr_SQLFILE.sql

Import: Release 18.0.0.0.0 - Production on Wed Feb 2 22:17:03 2022
Version 18.4.0.0.0

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

Username: hr@pdb1
Password:

Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Master table "HR"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_SQL_FILE_FULL_01":  hr/********@pdb1 directory=dbbackup dumpfile=hr_metadata.dmp sqlfile=hr_SQLFILE.sql
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/TABLESPACE_QUOTA
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/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/JOB
Job "HR"."SYS_SQL_FILE_FULL_01" successfully completed at Wed Feb 2 22:17:13 2022 elapsed 0 00:00:05

3. SQL file generated by IMPDP as shown below as example of first few lines:

-- CONNECT HR
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 "HR" IDENTIFIED BY VALUES 'S:4330197A1632F7C62909E527B20DA067F910DE80F801A7E670EEB0DA24FC;T:83BF412FB1FDA9D6756E252C7A9666A7681D9D1B4E39F223E070BC9E4BDC75A8EF2BBEB7C1D1E12DA1E2363AA6A3E9191803363E65A6A5D5799DE08573C3783603EB2B84020447709EF829F4FE1384F0'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT CREATE SESSION TO "HR";
GRANT ALTER SESSION TO "HR";
GRANT UNLIMITED TABLESPACE TO "HR";
GRANT CREATE TABLE TO "HR";
GRANT CREATE SYNONYM TO "HR";
GRANT CREATE VIEW TO "HR";
GRANT CREATE SEQUENCE TO "HR";
GRANT CREATE DATABASE LINK TO "HR";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "RESOURCE" TO "HR";
 GRANT "DBA" TO "HR";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "HR" DEFAULT ROLE ALL;
------------
------------

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.