Search This Blog

Friday, 3 March 2017

DBMS_METADATA.GET_DDL PACKAGE


DBMS_METADSATA package is used to get DDL of a particular object:



select dbms_metadata.get_ddl('DB_LINK','OBJECT_NAME','OWNER') from dual;
EXAMPLE:
select dbms_metadata.get_ddl('DB_LINK','CMIACC_CMIDEV','ESO_USER') from dual;

select dbms_metadata.get_ddl('MATERIALIZED_VIEW','OBJECT_NAME','OWNER') from dual;
EXAPPLE:
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MV_REPORT_PLANS','CMIACC') from dual;

select dbms_metadata.get_ddl('CLUSTER','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('CONTEXT','OBJECT_NAME') from dual;

select dbms_metadata.get_ddl('DB_LINK','OBJECT_NAME','PUBLIC') from dual;

select dbms_metadata.get_ddl('FUNCTION','OBJECT_NAME','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX','OBJECT_NAME','SYSTEM') from dual;

select dbms_metadata.get_ddl('JAVA_SOURCE','OBJECT_NAME','ADAM') from dual

select dbms_metadata.get_ddl('JAVA_SOURCE','OBJECT_NAME','SYS') from dual

select dbms_metadata.get_ddl('LIBRARY','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('OPERATOR','OBJECT_NAME') from dual;

select dbms_metadata.get_ddl('OPERATOR','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('PACKAGE','OBJECT_NAME') from dual;

select dbms_metadata.get_ddl('PACKAGE','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('PACKAGE_BODY','OBJECT_NAME','PERFSTAT') from dual;

select dbms_metadata.get_ddl('PROCEDURE','OBJECT_NAME','SYSTEM') from dual;

select dbms_metadata.get_ddl('SEQUENCE','OBJECT_NAME','PERFSTAT') from dual;

select dbms_metadata.get_ddl('SYNONYM','OBJECT_NAME','PUBLIC') from dual;

select dbms_metadata.get_ddl('TABLE','OBJECT_NAME','SYSTEM') from dual;

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

select dbms_metadata.get_ddl('TRIGGER','OBJECT_NAME','SYSTEM') from dual;

select dbms_metadata.get_ddl('TYPE','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('TYPE_BODY','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('VIEW','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('VIEW','OBJECT_NAME') from dual;

select dbms_metadata.get_ddl('PACKAGE','OBJECT_NAME','SYS') from dual;


Extract DDL for Users:

Let’s use DBMS_METADATA.GET_DDL package to extract CREATE USER DDL.
SQL> select dbms_metadata.get_ddl ('USER',username) as col1 from dba_users where username = 'SCOTT';

SYSTEM_GRANT:
select dbms_metadata.get_granted_ddl ('SYSTEM_GRANT',username) as col1 from dba_users where username  = 'SCOTT';

OBJECT_GRANT:
select dbms_metadata.get_granted_ddl('OBJECT_GRANT',username) as col1 from dba_users where username = 'SCOTT';

ROLES_GRANTED_TO_USERS: 
select dbms_metadata.get_granted_ddl('ROLE_GRANT',username) as col1 from dba_users where username  = 'SCOTT';

No comments:

Post a Comment