Extracting DDL using DBMS_METADATA and DATAPUMP

How to extract the table definition (DDL statement) from an Oracle database ?

DBMS_METADATA can be used to extract DDL definitions from a database. You can also use DataPump to extract the DDL.

 

DBMS_METADATA

set pagesize 0
set long 90000
set feedback off
select DBMS_METADATA.GET_DDL('TABLE','') from DUAL;

 

DATAPUMP

In this example we are exporting only the package "IFC". The output of the DDL is located as myoutput.txt .  The impdp keyword here is sqlfile

Create a parameter file ""parfile.parschemas=volors
directory=data_pump_dir
include=package:"= 'IFC'"

> expdp system/password parfile=c:\parfile.par

> impdp system/password directory=data_pump_dir sqlfile=myoutput.txt