Site icon IT Tutorial

How to Get DDL ( Create Script ) of any Object in Oracle Using DBMS_METADATA.GET_DDL

Mehmet Salih Deveci

Data Dictionary. Oracle server. Tables containing business data: EMPLOYEES. DEPARTMENTS. LOCATIONS. JOB_HISTORY. ... Data dictionary views: DICTIONARY. USER_OBJECTS. USER_TABLES. USER_TAB_COLUMNS. ... Data Dictionary. User tables are tables created by the user and contain business data, such as EMPLOYEES. There is another collection of tables and views in the Oracle database known as the data dictionary. This collection is created and maintained by the Oracle server and contains information about the database. The data dictionary is structured in tables and views, just like other database data. Not only is the data dictionary central to every Oracle database, but it is an important tool for all users, from end users to application designers and database administrators. You use SQL statements to access the data dictionary. Because the data dictionary is read-only, you can issue only queries against its tables and views. You can query the dictionary views that are based on the dictionary tables to find information such as: Definitions of all schema objects in the database (tables, views, indexes, synonyms, sequences, procedures, functions, packages, triggers, and so on) Default values for columns. Integrity constraint information. Names of Oracle users. Privileges and roles that each user has been granted. Other general database information. Oracle Database 11g: SQL Fundamentals II

I will explain How to Get DDL ( Create Script ) of any Object in Oracle Using DBMS_METADATA.GET_DDL in this article. Let’s go to review how to use Get DDL ( Create Script ) DBMS_METADATA.GET_DDL in Oracle.

 

DBMS_METADATA.GET_DDL

DBAs, Developers and DWH Teams need create scripts of Objects ( Table,index,package,tablespace and etc ) every time.

Oracle database has DBMS_METADATA package that provide a way for you to retrieve metadata from the database dictionary.

You can retrieve metadata of any object using Get DDL ( Create Script ) DBMS_METADATA.GET_DDL

 

Get DDL ( Create Script ) DBMS_METADATA.GET_DDL

Syntax of DBMS_METADATA.GET_DDL is as follows.

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;

DBMS_METADATA – User Create Script

You can get DDL ( Create Script  ) of any User ( Schema ) as follows.

select dbms_metadata.get_ddl('USER', du.username) AS DDL_SCRIPT
from dba_users du
where du.username = 'TYPE_USER_NAME'
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', dtq.username) AS DDL_SCRIPT
from dba_ts_quotas dtq
where dtq.username = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', dsp.grantee) AS DDL_SCRIPT
from dba_sys_privs dsp
where dsp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', dtp.grantee) AS DDL_SCRIPT
from dba_tab_privs dtp
where dtp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'TYPE_USER_NAME'
and drp.default_role = 'YES'
and rownum = 1;

 

GET_DDL

For example: You can get DDL of MEHMETSALIH user as follows.

 

SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('USER', du.username) AS DDL_SCRIPT
  2  from   dba_users du
  3  where  du.username = 'MEHMETSALIH'
  4  union all
  5  select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', dtq.username) AS DDL_SCRIPT
  6  from   dba_ts_quotas dtq
  7  where  dtq.username = 'MEHMETSALIH'
  8  and    rownum = 1
  9  union all
 10  select dbms_metadata.get_granted_ddl('ROLE_GRANT', drp.grantee) AS DDL_SCRIPT
 11  from   dba_role_privs drp
 12  where  drp.grantee = 'MEHMETSALIH'
 13  and    rownum = 1
 14  union all
 15  select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', dsp.grantee) AS DDL_SCRIPT
 16  from   dba_sys_privs dsp
 17  where  dsp.grantee = 'MEHMETSALIH'
 18  and    rownum = 1
 19  union all
 20  select dbms_metadata.get_granted_ddl('OBJECT_GRANT', dtp.grantee) AS DDL_SCRIPT
 21  from   dba_tab_privs dtp
 22  where  dtp.grantee = 'MEHMETSALIH'
 23  and    rownum = 1
 24  union all
 25  select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', drp.grantee) AS DDL_SCRIPT
 26  from   dba_role_privs drp
 27  where  drp.grantee = 'MEHMETSALIH'
 28  and    drp.default_role = 'YES'
 29  and    rownum = 1;

   CREATE USER "MEHMETSALIH" IDENTIFIED BY VALUES 'S:DFDE2EB3729B9D88FC8F7492942D2EA6476BF291FECB0DC56F2A64867F17;T:7D2C79E56B7427C7D5E4E2F209FE4F35524316F9116D15803A5AB1688CAB6DBB1BAC36145075F68C4521E1EA744F2BD53FA8BC08F4BC384B5A0E04D832D49E813BC849BF01C7F38DC780E9BC3C9391E2;8855019455223980'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PROFILE "PERSONAL";


   GRANT "DBA" TO "MEHMETSALIH";


  GRANT UNLIMITED TABLESPACE TO "MEHMETSALIH";


   ALTER USER "MEHMETSALIH" DEFAULT ROLE ALL;

SQL>

 

 

Get DDL of a Table ( Table Create Script )

You can get DDL ( Create Script  ) of any table as follows.

select dbms_metadata.get_ddl( 'TABLE', 'TABLE_NAME','SCHEMA_NAME' ) from dual;

 

For example; You can get MEHMETSALIH.OFFER_CLASS table create script as follows.

 

select dbms_metadata.get_ddl('TABLE', 'OFFER_CLASS','MEHMETSALIH') from dual;

 

SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('TABLE', 'OFFER_CLASS','MEHMETSALIH') from dual;

CREATE TABLE "MEHMETSALIH"."OFFER_CLASS"
( "OFFER_ID" VARCHAR2(30 CHAR) NOT NULL ENABLE,
"OFFER_CLASS_SCHEME_CD" VARCHAR2(16 CHAR) NOT NULL ENABLE,
"VALID_FROM_DT" DATE NOT NULL ENABLE,
"VALID_UNTIL_DT" DATE NOT NULL ENABLE,
"OFFER_CLASS_VALUE_CD" VARCHAR2(40 CHAR) NOT NULL ENABLE,
"SOURCE_SYSTEM_CD" VARCHAR2(5 CHAR) NOT NULL ENABLE,
"INSERT_DT" DATE,
"UPDATE_DT" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
COLUMN STORE COMPRESS FOR QUERY HIGH NO ROW LEVEL LOCKING NOLOGGING
STORAGE(INITIAL 81920 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"
PARALLEL 96

SQL>

 

You can get all tables get ddl ( create script ) or any schema’s tables create script as follows.

 

SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner)
FROM all_tables
WHERE owner = UPPER('&1');

 

 

 

Index Create Script

You can get DDL ( Create Script  ) of any index as follows.

select dbms_metadata.get_ddl( 'INDEX', 'INDEX_NAME','SCHEMA_NAME' ) from dual;

 

For example; You can get MEHMETSALIH.PK_OFFER_CLASS index  create script as follows.

 

 

SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('INDEX','PK_OFFER_CLASS','MEHMETSALIH') from dual;

CREATE UNIQUE INDEX "MEHMETSALIH"."PK_OFFER_CLASS" ON "MEHMETSALIH"."OFFER_CLASS" ("OFFER_CLASS_SCHEME_CD", "VALID_FROM_DT", "OFFER_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_USER" ;

SQL>

 

You can get all indexes get ddl ( create script ) as follows.

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE ='NORMAL';

 

Tablespace Create Script

You can generate all tablespaces get ddl ( create script ) as follows.

set head off echo off
select 'select dbms_metadata.get_ddl(''TABLESPACE'','''
|| tablespace_name || ''') from dual;' from dba_tablespaces;

 

You can get the Users tablespace get ddl ( create script ) as follows.

SQL> select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;

CREATE TABLESPACE "USERS" DATAFILE
SIZE 68717379584
AUTOEXTEND ON NEXT 68717379584 MAXSIZE 65535M,
SIZE 64424509440
AUTOEXTEND ON NEXT 64424509440 MAXSIZE 65535M,
SIZE 10737418240
AUTOEXTEND ON NEXT 10737418240 MAXSIZE 65535M
LOGGING ONLINE PERMANENT BLOCKSIZE 16384
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
COLUMN STORE COMPRESS FOR QUERY HIGH NO ROW LEVEL LOCKING SEGMENT SPACE MANAGEMENT AUTO;

 

DBMS_METADATA.GET_DDL VIEW, FUNCTION,PACKAGE

You can get all views get ddl ( create script ) as follows.

SELECT DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME) FROM USER_VIEWS;

 

You can get all functions, procedures get ddl ( create script ) as follows.

SELECT DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME) FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION';

 

You can get any package’s get ddl ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','OBJECT_NAME','SCHEMA_NAME') FROM DUAL;

 

 

You can get any package’s body get ddl ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','OBJECT_NAME','SCHEMA_NAME') FROM DUAL;

 

You can get any constraint’s get ddl ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','OBJECT_NAME','SCHEMA_NAME') from dual;

 

You can get any user’s system grant ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCHEMA_NAME') from dual;

 

You can get any user’s role grant ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCHEMA_NAME') from dual;

 

If you want to move tables, Index or partitions to Another tablespace, then read the following post.

How to Move Tables, Index Rebuild, Partitions and Subpartitions to Another Tablespace in Oracle

 

 

 

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

Exit mobile version