NaturalOracle DataGaurd tutorial Series Coming Soon!! Natural
For Any queries, please mail us at support@funoracleapps.com

Getting Source for Objects in Oracle Database

1 comment
The source\syntax for any objects(like table,package,synonyms etc..) in the database can be found by 

 
Syntax:

SELECT DBMS_METADATA.GET_DDL('Object_Type','Object_Name','Object_Owner') FROM dual;

Example: 
set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','SYS') FROM dual;


Object Types:

Object types can be any of the following :


ASSOCIATION
AUDIT
AUDIT_OBJ
CLUSTER
COMMENT
CONSTRAINT
CONTEXT
DB_LINK
DEFAULT_ROLE
DIMENSION
DIRECTORY
FUNCTION
INDEX
INDEXTYPE
JAVA_SOURCE
LIBRARY
MATERIALIZED_VIEW
MATERIALIZED_VIEW_LOG
OBJECT_GRANT
OPERATOR
OUTLINE
PACKAGE
PACKAGE_SPEC
PACKAGE_BODY
PROCEDURE
PROFILE
PROXY
REF_CONSTRAINT
ROLE
ROLE_GRANT
ROLLBACK_SEGMENT
SEQUENCE
SYNONYM
SYSTEM_GRANT
TABLE
TABLESPACE
TABLESPACE_QUOTA
TRIGGER
TRUSTED_DB_LINK
TYPE
TYPE_SPEC
TYPE_BODY
USER
VIEW

In older version of Oracle 

We can get the source by dba_source\all_source table.

Syntax:

set pagesize 0
set long 90000
SELECT text FROM dba_source WHERE NAME='Object_Name' AND OWNER='Object_Owner' and TYPE='Object_Type;

1 comment :