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;