IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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 )
