How to get DDL of Tablespace present in Oracle Database

Fetch the DDL commands for all or one tablespace in Oracle

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

Note: If you want the Semicolon after every statement generated then you can execute the following command at your sessionEnable the SQL Terminator after each object syntax completed.
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

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;
--Example:
--Enter the tablespace Name then it will get you the DDL for your tablespace.
SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','&TABLESPACE_NAME') FROM dual;
Enter value for tablespace_name: SYSAUX
old 1: SELECT DBMS_METADATA.GET_DDL('TABLESPACE','&TABLESPACE_NAME') FROM dual
new 1: SELECT DBMS_METADATA.GET_DDL('TABLESPACE','SYSAUX') FROM dual
DBMS_METADATA.GET_DDL('TABLESPACE','SYSAUX')
--------------------------------------------------------------------------------
CREATE TABLESPACE "SYSAUX" DATAFILE
'C:\ORACLE\ORADATA\XE\SYSAUX01.DBF' SIZE 419430400
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

This entry was posted in Oracle on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply