• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Get Table,View, Materialized View, Package, Index Definition in Oracle

Get Table,View, Materialized View, Package, Index Definition in Oracle

June 12, 2020 by techgoeasy Leave a Comment

We often need to get the definition of the table, index, views, etc in oracle to replicate the structure in another Oracle database. Oracle has a very useful command to find the metadata definition of the various structure. This command is very useful in extracting the metadata of any objects like table, index, views, Materialized views

select dbms_metadata.get_ddl ('<OBJECT_TYPE>','<OBJECT_NAME>','<OBJECT_OWNER>') from dual;

The output of the query is of the LONG character type. So please set the long length before starting the query

Table of Contents

  • Get definition example
    • how to get table definition in oracle
    • oracle show index definition
    • how to get ddl of materialized view in oracle
    • How to extract package definition from database in Oracle
    • How to extract package body definition from database in Oracle
    • How to extract user creation command from database in Oracle
    • How to extract tablespace creation command from database in Oracle
    • how to get the query of a view in oracle
    • How to extract trigger creation command from database in Oracle
    • how to get profile metadata in oracle
  • How to format the definition statement

Get definition example

how to get table definition in oracle

Here is how to get create a table script in oracle

Syntax

set long 2000

select dbms_metadata.get_ddl('TABLE','<TABLE NAME>','OWNER') from dual;

It is very important to set a long column as the output is the long type and it will be truncated if don’t specify the write numbers

Example

set long 2000

select dbms_metadata.get_ddl('TABLE','FND_USERS','APPLSYS') from dual;
select dbms_metadata.get_ddl('TABLE','FND_APPLICATIONS','APPLSYS') from dual;
select dbms_metadata.get_ddl('TABLE','FND_NODES','APPLSYS') from dual;

oracle show index definition

Syntax

set long 2000

select dbms_metadata.get_ddl('INDEX','<INDEX NAME>','OWNER') from dual;

It is very important to set a long column as the output is the long type and it will be truncated if don’t specify the write numbers
Example

set pagesize 0

set long 2000

select dbms_metadata.get_ddl('INDEX','FND_USERS_N1','APPLSYS') from dual;

how to get ddl of materialized view in oracle

set pagesize 0

set long 2000

select dbms_metadata.get_ddl('MATERIALIZED VIEW',KIN_SUM_MV','APPS') from dual;

How to extract package definition from database in Oracle

set pagesize 0
set long 10000
SELECT DBMS_METADATA.GET_DDL('PACKAGE','FND_PACK','APPS) FROM dual;

How to extract package body definition from database in Oracle

set pagesize 0
set long 10000
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','FND_PACK','APPS) FROM dual;

How to extract user creation command from database in Oracle

set pagesize 0
set long 10000
SELECT DBMS_METADATA.GET_DDL('USER','SCOTT') FROM dual;

How to extract tablespace creation command from database in Oracle

set pagesize 0
set long 10000
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','<TNAME>') FROM dual;

how to get the query of a view in oracle

set pagesize 0
set long 10000
SELECT DBMS_METADATA.GET_DDL('VIEW','<VIEW_NAME>','<OWNER') FROM dual;

How to extract trigger creation command from database in Oracle

set pagesize 0
set long 10000
SELECT DBMS_METADATA.GET_DDL('TRIGGER','<VIEW_NAME>','<OWNER') FROM dual;

how to get profile metadata in oracle

set pagesize 0
set long 10000
SELECT DBMS_METADATA.GET_DDL('PROFILE','') from dual;

How to format the definition statement

The output generated by the above statement can be made more clear by using the below command before running the metadata command

BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

If you don’t want the tablespace, segment, and storage things in the get ddl command, we can use the below command

BEGIN
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', FALSE)
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', FALSE)
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', FALSE)
END;
/

Likewise, we have a command for all the object creation in the Oracle database. This is quite a powerful command and we can use it to extract the table definition from all the tables in the schema also.

Related Articles
check oracle table size
how to check tablespace in oracle
Oracle Sql tutorial
how to write sql queries
how to check trigger status in oracle
https://docs.oracle.com/database/121/ARPLS/d_metada.htm

Filed Under: Oracle, Oracle Database Tagged With: how to find the metadata of any object, metadata

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to generate sql monitor report in Oracle
  • How to find the long running (longops session) in Oracle
  • How to find concurrent request id from Sid or spid
  • how to check recycle bin in oracle: Purge, enable/disable
  • how to check fra size in oracle: Location, usage, change size

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us