Getting Source for Objects in Oracle Database
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;
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
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
set long 90000
SELECT text FROM dba_source WHERE NAME='Object_Name' AND OWNER='Object_Owner' and TYPE='Object_Type;
Subscribe to:
Post Comments
(
Atom
)
Thanks for sharing this, it is really helpful.
ReplyDeleteOracle Training in Chennai | Oracle course in Chennai | Oracle DBA Training in Chennai
Thanks for taking time to share this useful blog...
ReplyDeleteDOT NET Training in Chennai
DOT NET Training in Bangalore
.net coaching centre in chennai
dot net training institutes in bangalore
aws training in bangalore
Data Science Courses in Bangalore
DevOps Training in Bangalore
PHP Training in bangalore
spoken english classes in bangalore
dot net course in bangalore
Great post. keep sharing such a worthy information
ReplyDeleteTally Classes in Chennai
Tally Course in chennai
Tally training coimbatore
Tally course in madurai
Tally Course in Hyderabad
Tally Training in Chennai
Tally Course in Coimbatore
Tally Training in Hyderabad
ielts coaching centre in coimbatore
Ethical hacking course in bangalore