Sunday, September 14, 2008

Extract DDL from database objects

Easy steps to extract ddl from database objects:


In order to extract ddl we have to use GET_DDL function of DBMS_METADATA package

sample syntax

DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','SCHEMA')

Note: all values should be in varchar2 (means accepted as character,so mention it in singe quotes(' ') and upper case) and schema is by default current schema(means if no schema is mentioned,it will run in current schema)

Check some examples:


a) Extract DDL from all the tables of a schema:

sql>
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,u.owner)
FROM DBA_TABLES u WHERE u.owner='&owner';



It will prompt for schema name , mention it as below
example:
Enter value for owner: SCOTT



b) Extract DDL from all the Indexes of a schema:

sql>
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,u.owner)
FROM DBA_indexeS u WHERE u.owner='&owner';



It will prompt for schema name , mention it as below
example:
Enter value for owner: SCOTT



c) Extract DDL from a specific table of a schema:

sql>
SELECT DBMS_METADATA.GET_DDL('TABLE','&table_name','&owner') from dual;

It will prompt for schema name , mention it as below
example:
Enter value for owner: SCOTT
Enter value for table_name: EMP



d) Or we can try the following to Extract DDL from all the tables of current schema:

sql>SELECT DBMS_METADATA.GET_DDL('TABLE',a.table_name)
FROM USER_ALL_TABLES a
WHERE a.nested='NO' AND (a.iot_type is null or a.iot_type='IOT') ;

Note: I have excluded nested tables and overflow segments here


e) Extract DDL for all the system privilege granted to scott


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

OUTPUT:

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
----------------------------------------------------------------------------
GRANT CREATE ANY DIRECTORY TO "SCOTT"
GRANT CREATE SYNONYM TO "SCOTT"



f) Extract DDL from all the Procedures of a schema

sql>select DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name,u.owner)
from dba_objects u where u.object_type = 'PROCEDURE' and u.owner='&owner';


g) Extract DDL from all the Function of a schema

sql>SELECT DBMS_METADATA.GET_DDL('FUNCTION',u.object_name,u.owner)
FROM DBA_objects u WHERE u.object_type='FUNCTION' and u.owner='&owner';



h) Extract DDL from all the Synonym of a schema


sql>SELECT DBMS_METADATA.GET_DDL('SYNONYM',u.SYNONYM_name,u.owner)
FROM DBA_synonyms u WHERE u.owner='&owner';


i) Extract DDL from all the Sequence of a schema

sql> SELECT DBMS_METADATA.GET_DDL('SEQUENCE',u.Sequence_name,u.sequence_owner)
FROM DBA_sequences u WHERE u.sequence_owner='&owner';





j) MORE:

For a Role:
sql> select DBMS_METADATA.GET_DDL('ROLE','ROLLNAME') FROM DUAL;

For a View:
sql> SELECT DBMS_METADATA.GET_DDL('VIEW','VIEWNAME','SCHEMA') FROM DUAL;

For a Synonym:
sql>SELECT DBMS_METADATA.GET_DDL('SYNONYM','NAME','SCHEMA') FROM DUAL;



SOME OTHER OBJECT TYPE WHAT WE CAN USE:


AUDIT
AUDIT_OBJ
CLUSTER
COMMENT
CONSTRAINT
DB_LINK
DEFAULT_ROLE
DIRECTORY
FUNCTION
INDEX_STATISTICS
INDEX_TYPE
OBJECT_GRANT
PACKAGE
PROCEDURE
PROFILE
ROLE
ROLE_GRANT
ROLLBACK_SEGMENT
SEQUENCE
SYNONYM
SYSTEM_GRANT
TABLE
TABLE_DATA
TABLE_STATISTICS
TABLESPACE
TABLESPACE_QUOTA
TRIGGER
USER
VIEW



Have A Nice Day.......................

A wise man will make more opportunities than he finds.
Francis Bacon