We often need to find the definition of the table ,index ,views etc 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 LONG character type. So please set the long length before starting the query
Extracting definition example
How to extract Table definition in Oracle
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;
How to extract Index definition in Oracle
set pagesize 0
set long 2000
select dbms_metadata.get_ddl('INDEX','FND_USERS_N1','APPLSYS') from dual;
How to extract Materialized view definition 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 extract view creation command from database 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;
Like wise we have a command for all the object creation in the Oracle database. This is quite powerful command and we can use it extract the table definition from all the tables in the schema also.
Related Articles
Query to check table size in Oracle
Tablespace Name ,Size ,Free space,datafiles ,growth in Oracle Database
Sql tutorial for Oracle ,MySQL , SQL server and Postgres
Leave a Reply