Oracle Database Experts

You can use SQL to generate SQL for DDL for database objects. Here’s another alternative using the DBMS_METADATA package:

----------------------------------------------------------------------------------------------
--
SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT', 'ORDERS', 'OE') from dual;
SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('TRIGGER','<table_name>','<schema>') from dual;

----------------------------------------------------------------------------------------------
--
set pagesize 0
set long 90000
set feedback off
set echo off 

spool schema.sql 

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
     FROM USER_INDEXES u;

spool off;

----------------------------------------------------------------------------------------------
--
SELECT dbms_metadata.get_dependent_xml('OBJECT_GRANT', 'ORDERS', 'OE') from dual;

----------------------------------------------------------------------------------------------
--  View system grants for a user
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'OE_USER') FROM dual

----------------------------------------------------------------------------------------------
--
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT', 'OE') from dual;

SELECT dbms_metadata.get_ddl('VIEW', 'MY_TABLES')
FROM dual;

Function
CREATE OR REPLACE FUNCTION whoami RETURN VARCHAR2 IS

BEGIN
   RETURN user;
END whoami;
/

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

# --------------------------------------------------------------------------------------------
# For maximum flexibility, extract object definitions for tables, indexes, and constraints
exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'PRETTY', true);

exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', true);
exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'STORAGE', true);
exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'TABLESPACE', true);

exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'CONSTRAINTS', true);
exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'REF_CONSTRAINTS', true);
exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', true);

Posted by Charles Kim, Oracle ACE