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