How to Extract any object definition in Oracle

Sometimes when you don't have access to tools like PL/SQL Developer or any other GUI that can help you see the definition of a certain objects we can use GET_DDL() function from the metadata package DBMS_METADATA. This article might be very basic for some DBA's but it will for sure help newly comes into DBA's world.     I will start talking about the DBMS_METADATA and the GET_DDL() usage and basic syntax. DBMS_METADATA - package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object. GET_DDL() - function allow us to extract the creation DDL for an object. Syntax: 

select DBMS_METADATA.GET_DDL('TABLE','') from DUAL;
Full Syntax with all arguments and options that can be used: 
DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
We can extract the definition of the following objects using GET_DDL function:   TABLE, INDEXES, CONSTRAINT, CONTEXT, DB_LINK, ROLES, DIMENSION, DIRECTORY, FUNCTION, INDEXTYPE, JAVA_SOURCE ,LIBRARY,MATERIALIZED_VIEW, OBJECT_GRANT, PACKAGE,PACKAGE_SPEC,PACKAGE_BODY,PROCEDURE,PROFILE, SEQUENCE, SYNONYM, TRIGGER, USER, VIEW, TYPE_BODY,TYPE, TABLESPACE,...there are few more. Examples of how we can use the GET_DDL function to extract the object definition: Note: while working with sqlplus we need to setup the option set pagesize and long values as bellow. 
set pagesize 0
set long 1200
  • Extract a table definition
 set pagesize 0
 set long 1200
 select DBMS_METADATA.GET_DDL('TABLE','AW$') from DUAL;

  CREATE TABLE "SYS"."AW$"
   (    "AWNAME" VARCHAR2(30),
        "OWNER#" NUMBER NOT NULL ENABLE,
        "AWSEQ#" NUMBER NOT NULL ENABLE,
        "VERSION" NUMBER DEFAULT null,
        "OIDS" NUMBER(10,0) DEFAULT null,
        "OBJS" NUMBER(10,0) DEFAULT null,
        "DICT" RAW(8) DEFAULT null,
        "RSYGEN" NUMBER DEFAULT null
   ) 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 "SYSTEM"
  • Extract a INDEX definition
 set pagesize 0
 set long 1200
 select DBMS_METADATA.GET_DDL('INDEX','I_TS#') from DUAL;

  CREATE INDEX "SYS"."I_TS#" ON CLUSTER "SYS"."C_TS#"
  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 "SYSTEM"
  • Extract a SEQUENCE definition
 set pagesize 0
 set long 1200
 select DBMS_METADATA.GET_DDL('SEQUENCE','JOBSEQ') from DUAL;

   CREATE SEQUENCE  "SYS"."JOBSEQ"  MINVALUE 1 MAXVALUE 999999999 INCREMENT BY 1
 START WITH 154 CACHE 20 NOORDER  CYCLE
  • Extract a VIEW definition
 select DBMS_METADATA.GET_DDL('VIEW','V_$PARAMETER') from DUAL;

  CREATE OR REPLACE FORCE VIEW "SYS"."V_$PARAMETER" ("NUM", "NAME", "TYPE", "VAL
UE", "DISPLAY_VALUE", "ISDEFAULT", "ISSES_MODIFIABLE", "ISSYS_MODIFIABLE", "ISIN
STANCE_MODIFIABLE", "ISMODIFIED", "ISADJUSTED", "ISDEPRECATED", "ISBASIC", "DESC
RIPTION", "UPDATE_COMMENT", "HASH") AS
  select "NUM","NAME","TYPE","VALUE","DISPLAY_VALUE","ISDEFAULT","ISSES_MODIFIAB
LE","ISSYS_MODIFIABLE","ISINSTANCE_MODIFIABLE","ISMODIFIED","ISADJUSTED","ISDEPR
ECATED","ISBASIC","DESCRIPTION","UPDATE_COMMENT","HASH" from v$parameter

Copyright 2020. All Rights Reserved