Get DDL of all objects in one script of Schema in Oracle
- 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;
------------
------------