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';