Tag Archives: DBMS METADATA

Get DDL Scripts of objects table, index, constraints, trigger, tablespace, users for Oracle

Leave a reply

Get DDL Scripts of Object present in Oracle through DBMS_METADATA Function

Dbms_metadata function is used to get the ddl of all the objects through sql prompt. It is very helpful for DBA’s if they do not have any tool to use such toad, sql developer.

Syntax:
SELECT DBMS_METADATA.get_ddl (object_type, object_name, owner) FROM dual;

Generate the DDL for table command in Oracle

SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL('TABLE','') from DUAL;

SET LONG 20000
set LONGCHUNKSIZE 20000
set PAGESIZE 0
set LINESIZE 1000
set FEEDBACK OFF
set VERIFY OFF
SELECT SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner) FROM dual;

Generate the DDL for Table Partition DDL Script.

Dbms_metadata function is used to get the DDL of all the objects through SQL prompt. It is very helpful for DBA’s if they do not have any tool to use such toad, SQL Developer.

Syntax:
SELECT DBMS_METADATA.get_ddl (object_type, object_name, owner) FROM dual;

Generate the DDL for table command in Oracle

SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL('TABLE','') from DUAL;

SET LONG 20000
set LONGCHUNKSIZE 20000
set PAGESIZE 0
set LINESIZE 1000
set FEEDBACK OFF
set VERIFY OFF
SELECT SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner) FROM dual;

Generate the DDL for Table Partition DDL Script.

SET LONG 20000
set LONGCHUNKSIZE 20000
set PAGESIZE 0
set LINESIZE 1000
set FEEDBACK OFF
set VERIFY OFF
select DBMS_METADATA.GET_DDL('TABLE PARTITION','TRAN','IC') from dual;

Generate the Index DDL command.

set long 1000
set pagesize 0
select DBMS_METADATA.GET_DDL('INDEX','PERSON_IDX') from DUAL;
SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner) FROM dual;

Get Constraint DDL script

SET LONG 20000
set LONGCHUNKSIZE 20000
set PAGESIZE 0
set LINESIZE 1000
set FEEDBACK OFF
set VERIFY OFF
SELECT DBMS_METADATA.get_ddl ('CONSTRAINT', constraint_name, owner) FROM dual;

Generate the Trigger DDL Script

SET LONG 20000
set LONGCHUNKSIZE 20000
set PAGESIZE 0
set LINESIZE 1000
set FEEDBACK OFF
set VERIFY OFF
SELECT DBMS_METADATA.get_ddl ('TRIGGER', trigger_name, owner) FROM dual;

Generate the Sequence DDL Script

SET LONG 20000
set LONGCHUNKSIZE 20000
set PAGESIZE 0
set LINESIZE 1000
set FEEDBACK OFF
set VERIFY OFF
select dbms_metadata.get_ddl('SEQUENCE','POS_PAY_ITEM_NO_SEQ','IC') from dual;

Generate the DDL for function Script

SELECT dbms_metadata.get_ddl('FUNCTION', 'WHOAMI') FROM DUAL;

Get Tablespace DDL Script:

SET LONG 20000
set LONGCHUNKSIZE 20000
set PAGESIZE 0
set LINESIZE 1000
set FEEDBACK OFF
set VERIFY OFF
SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name) FROM dual;

Get USER Grant DDL Script:

set heading off
set pages 0
set long 9999999
select dbms_metadata.get_granted_ddl('ROLE_GRANT', '&&user') from dual
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', '&&user') from dual
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT','&&user') from dual;

Get USER Grant include quota DDL Script:

set long 20000
set longchunksize 20000
set pagesize 0
set linesize 1000
set trimspool on
set column ddl format a1000
set feedback off
set verify off

select dbms_metadata.get_ddl('USER', u.username) AS ddl
from dba_users u
where u.username = '&&v_username'
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from dba_ts_quotas tq
where tq.username = '&&v_username' and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = '&&v_username'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = '&&v_username'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = '&&v_username'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = '&&v_username'
and rp.default_role = 'YES'
and rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from dba_users u
where u.username = '&&v_username'
and u.profile='DEFAULT'
and rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from dba_users u
where u.username = '&&v_username'
and u.profile='DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from dba_users u
where u.username = '&&v_username'
and u.profile='DEFAULT'
and rownum = 1
/