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 structures. 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
How to get table definition in Oracle
Here is how to 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 correct value
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 correct value
Example
set pagesize 0
set long 2000
select dbms_metadata.get_ddl('INDEX','FND_USERS_N1','APPLSYS') from dual;
How to get ddl of a 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 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 commands 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','<Profile name>') from dual;
How to format the definition statement
The output generated by the above statement can be made 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
How to find segment name from file id and block number in Oracle
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