How to Get ddl of any Object in Oracle: In this article we will explain about How to Get ddl of any Object in Oracle using DBMS_METADATA.GET_DDL.
Use of DBMS_METADATA:
Every time all DBAs and Developers required to create scripts of objects like Tables, Indexes, Packages, Packages Body etc. In Oracle database has a package name DBMS_METADATA and using this package we could find the metadata of any objects.
Get ddl of a Table:
Using below command you could get DDL of any tables.
select dbms_metadata.get_ddl(‘TABLE’,’TABLE_NAME’,’SCHEMA_NAME’) from dual;
SQL> set heading off;
SQL> set echo off;
SQL> set lines 1000 pages 40000
SQL> set long 999999
SQL> spool ddl_employees_table.sql
SQL> select dbms_metadata.get_ddl( 'TABLE','EMPLOYEES','HR' ) from dual;
CREATE TABLE "HR"."EMPLOYEES"
( "EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0),
CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
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 "SYSAUX" ENABLE,
CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE,
CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE,
CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
) 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 "SYSAUX"
CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
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 "SYSAUX"
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOY
EE_ID")
USING INDEX "HR"."EMP_EMP_ID_PK" ENABLE
SQL> spool off;
You could get all tables ddl in a SCHEMA using below command.
select dbms_metadata.get_ddl(‘TABLE’,’TABLE_NAME’,’OWNER’) from all_tables where owner=UPPER(‘&1’);
Get ddl of a Index:
Use below command to get DDL of any index in oracle database.
SQL> set heading off;
SQL> set echo off;
SQL> set lines 1000 pages 4000
SQL> set long 999999
SQL> spool ddl_index_REG_ID_PK.sql
SQL> select dbms_metadata.get_ddl('INDEX','REG_ID_PK','HR') from dual;
CREATE UNIQUE INDEX "HR"."REG_ID_PK" ON "HR"."REGIONS" ("REGION_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
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 "SYSAUX"
SQL> spool off;
Get ddl of a View:
Use below command to get DDL of all views in oracle database.
SQL> set heading off;
SQL> set echo off;
SQL> set lines 1000 pages 40000
SQL> set long 999999
SQL> spool all_view_ddl.sql
SQL> select dbms_metadata.get_ddl('VIEW', VIEW_NAME) from user_views;
Get ddl of a Procedure in oracle:
Using below command we can get a procedure ddl in oracle database.
select dbms_metadata.get_ddl(‘PROCEDURE’,’OBJECT_NAME’,’SCHEMA_NAME’) from dual;
SQL> SELECT DBMS_METADATA.GET_DDL('PROCEDURE','SECURE_DML','HR') FROM DUAL;
DBMS_METADATA.GET_DDL('PROCEDURE','SECURE_DML','HR')
--------------------------------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PROCEDURE "HR"."SECURE_DML"
IS
BEGIN
IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205,
'You may only make changes during normal office hours');
END IF;
END secure_dml;
SQL>Get ddl of a Package and package body in oracle:
Using Below queries you can find the package and package body ddl in oracle.
select dbms_metadata.get_ddl(‘PACKAGE’,’OBJECT_NAME’,’SCHEMA_NAME’) from dual;
select dbms_metadata.get_ddl(‘PACKAGE_BODY’,’OBJECT_NAME’,’SCHEMA_NAME’) from dual;
SQL> set heading off;
SQL> set echo off;
SQL> set lines 1000 pages 40000
SQL> set long 999999
SQL> spool utl_file_package_ddl.sql
SQL> select dbms_metadata.get_ddl('PACKAGE','UTL_FILE','SYS') from dual;
DBMS_METADATA.GET_DDL('PACKAGE','UTL_FILE','SYS')
--------------------------------------------------------------------------------
CREATE OR REPLACE NONEDITIONABLE PACKAGE "SYS"."UTL_FILE"
CREATE OR REPLACE NONEDITIONABLE PACKAGE BODY "SYS"."UTL_FILE"
SQL>spool off;
SQL>
SQL>
SQL>
SQL> set heading off;
SQL> set echo off;
SQL> set lines 1000 pages 4000
SQL> set long 999999
SQL> spool utl_file_package_body_ddl.sql
SQL> select dbms_metadata.get_ddl('PACKAGE_BODY','UTL_FILE','SYS') from dual;
SQL>spool off;
How to get User/Schema ddl in Oracle Database:
How to get Tablespace ddl in Oracle Database:
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 can write to mail me through : orakldba@gmail.com.