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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.