Wednesday, September 4, 2013

Extract DDL for schema and all objects in oracle database

The DDL script of the schema 11gr2

select dbms_metadata.get_ddl( 'USER','schema_name' ) from dual
     select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','schema_name') from dual
          UNION ALL
          select dbms_metadata.get_granted_ddl('OBJECT_GRANT','schema_name') from dual
               UNION ALL
               select dbms_metadata.get_granted_ddl('ROLE_GRANT','schema_name') from dual;

DDL of the objects present in a schema:-11gr2
SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner) FROM dba_OBJECTS   WHERE (OWNER = 'schema_name') and object_type not in ('LOB','TABLE PARTITION','INDEX PARTITION');

Note:- You can not take table, index partition and LOB objects as they are part of the object creation.

No comments:

Post a Comment