Run the following PL/SQL code so that the output will be in good format SQL> BEGIN dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE ); dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true ); dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false ); dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE ); dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false ); END; / SQL> set long 9999999 SQL> set pagesize 0 SQL> set linesize 120
| Object Type | Description | Example |
|---|---|---|
| ASSOCIATION | associate statistics | - |
| AUDIT | audits of SQL statements | - |
| AUDIT_OBJ | audits of schema objects | - |
| CLUSTER | clusters | select dbms_metadata.get_ddl('CLUSTER', CLUSTER_NAME,owner) from dba_clusters; |
| COMMENT | comments | - |
| CONSTRAINT | constraints | select dbms_metadata.get_ddl('CONSTRAINT',constraint_name,owner) from user_constraints where table_name='table name' |
| CONTEXT | application contexts | select dbms_metadata.get_ddl('CONTEXT', namespace, schema) from dba_context; |
| DB_LINK | database links | select dbms_metadata.get_ddl('DB_LINK', db_link, owner) from dba_db_links where rownum<2; |
| DEFAULT_ROLE | default roles | - |
| DIMENSION | dimensions | - |
| DIRECTORY | directories | select dbms_metadata.get_ddl('DIRECTORY', directory_name) from dba_directories where rownum<2; |
| FUNCTION | stored functions | - |
| INDEX | indexes | select dbms_metadata.get_ddl('INDEX', index_name, owner) from dba_indexes where rownum<2; |
| INDEXTYPE | indextypes | select dbms_metadata.get_ddl('INDEXTYPE', INDEXTYPE_NAME, owner) from dba_indextypes where rownum<2; |
| JAVA_SOURCE | java sources | select dbms_metadata.get_ddl('JAVA_SOURCE', object_name, owner) from dba_objects where object_type='JAVA SOURCE' and rownum<2; |
| LIBRARY | external procedure libraries | select dbms_metadata.get_ddl('LIBRARY', library_name) from dba_libraries where rownum<2; |
| MATERIALIZED_VIEW | materialized views | select dbms_metadata.get_ddl('MATERIALIZED_VIEW', mview_name, owner) from dba_mviews where rownum<2; |
| MATERIALIZED_VIEW_LOG | materialized view logs | select dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG', log_table, log_owner) from dba_mview_logs where rownum<2; |
| OBJECT_GRANT | object grants | - |
| OPERATOR | operators | select dbms_metadata.get_ddl('OPERATOR', OPERATOR_NAME, owner) from dba_operators where rownum<2; |
| OUTLINE | stored outlines | - |
| PACKAGE | stored packages | - |
| PACKAGE_SPEC | package specifications | select dbms_metadata.get_ddl('PACKAGE_SPEC', object_name, owner) from dba_objects where object_type='PACKAGE' and rownum<2; |
| PACKAGE_BODY | package bodies | select dbms_metadata.get_ddl('PACKAGE_BODY', object_name, owner) from dba_objects where object_type='PACKAGE' and rownum<2; |
| PROCEDURE | stored procedures | select dbms_metadata.get_ddl('PROCEDURE', object_name, owner) from dba_objects where object_type='PROCEDURE' and rownum<2; |
| PROFILE | profiles | select dbms_metadata.get_ddl('PROFILE', profile) from dba_profiles where rownum<2; |
| PROXY | proxy authentications | - |
| REF_CONSTRAINT | referential constraint | select dbms_metadata.get_ddl('REF_CONSTRAINT', constraint_name, owner) from dba_constraints where constraint_type='R' and rownum<2; |
| ROLE | roles | - |
| ROLE_GRANT | role grants | - |
| ROLLBACK_SEGMENT | rollback segments | select dbms_metadata.get_ddl('ROLLBACK_SEGMENT', segment_name) from dba_rollback_segs; |
| SEQUENCE | sequences | select dbms_metadata.get_ddl('SEQUENCE', sequence_name, owner) from dba_sequences; |
| SYNONYM | synonyms | select dbms_metadata.get_ddl('SYNONYM', synonym_name, owner) from dba_synonyms where rownum<2; |
| SYSTEM_GRANT | system privilege grants | - |
| TABLE | tables | select dbms_metadata.get_ddl('TABLE', table_name, owner) from dba_tables where rownum<2; |
| TABLESPACE | tablespaces | select dbms_metadata.get_ddl('TABLESPACE', tablespace_name) from dba_tablespaces where rownum<2; |
| TABLESPACE_QUOTA | tablespace quotas | - |
| TRIGGER | triggers | select dbms_metadata.get_ddl('TRIGGER', trigger_name, owner) from dba_triggers where rownum<2; |
| TRUSTED_DB_LINK | trusted links | - |
| TYPE | user-defined types | select dbms_metadata.get_ddl('TYPE', type_name, owner) from dba_types; |
| TYPE_SPEC | type specifications | - |
| TYPE_BODY | type bodies | - |
| USER | users | select dbms_metadata.get_ddl('USER', username) from dba_users; |
| VIEW | views | select dbms_metadata.get_ddl('VIEW', view_name, owner) from dba_views; |
| XMLSCHEMA | XML schema | - |
| AQ_QUEUE | queues |
| AQ_QUEUE_TABLE | additional metadata for queue tables |
| AQ_TRANSFORM | transforms |
| DATABASE_EXPORT | all metadata objects in a database |
| FGA_POLICY | fine-grained audit policies |
| INDEX_STATISTICS | indextypes |
| JOB | scheduler jobs (Current support scheduled for 11.2 .. See NOTE:567504.1) |
| REFRESH_GROUP | refresh groups |
| RESOURCE_COST | resource cost info |
| RLS_CONTEXT | driving contexts for enforcement of fine-grained access-control policies |
| RLS_GROUP | fine-grained access-control policy groups |
| RLS_POLICY | fine-grained access-control policies |
| RMGR_CONSUMER_GROUP | resource consumer groups |
| RMGR_INTITIAL_CONSUMER_GROUP | assign initial consumer groups to users |
| RMGR_PLAN | resource plans |
| RMGR_PLAN_DIRECTIVE | resource plan directives |
| SCHEMA_EXPORT | sequences |
| TABLE_DATA | metadata describing row data for a table, nested table, or partition |
| TABLE_EXPORT | metadata for a table and its associated objects |
| TABLE_STATISTICS | precomputed statistics on tables |
| TRANSPORTABLE_EXPORT | metadata for objects in a transportable tablespace set |