DDL

 ###### Tablespace ddl ############

Get the DDL of all the Tablespaces present in Oracle

set echo off;

Set pages 999;

set long 90000;

spool ddl_tablespace.sql

select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;

spool off

Get the DDL for specific tablespace present in Oracle

set echo off;

Set pages 999;

set long 90000;

SELECT DBMS_METADATA.GET_DDL('TABLESPACE','&TABLESPACE_NAME') FROM dual;

####### Table DDL #####

SET LONG 20000

set LONGCHUNKSIZE 20000

set PAGESIZE 0

set LINESIZE 1000

set FEEDBACK OFF

set VERIFY OFF

SELECT SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner) FROM dual;

##### Table Partition #####

SET LONG 20000

set LONGCHUNKSIZE 20000

set PAGESIZE 0

set LINESIZE 1000

set FEEDBACK OFF

set VERIFY OFF

select DBMS_METADATA.GET_DDL('TABLE PARTITION','TRAN','IC') from dual;

###### Index DDL ####

set long 1000

set pagesize 0

select DBMS_METADATA.GET_DDL('INDEX','PERSON_IDX') from DUAL;