How to get tablespace DDL in oracle

To get the tablespace DDL (Data Definition Language) in Oracle, you can use the DBMS_METADATA package. “tablespace DDL in oracle”

Get DDL for specific tablespace

Step 1: To get tablespace ddl in oracle first connect to your Oracle database using a tool such as SQL*Plus or SQL Developer.

SQL> sqlplus / as sysdba

Step 2: Execute the following SQL statement to set the output format for the DDL statement:

SQL> SET LONG 10000

This sets the maximum length of the output to 10,000 characters, which should be sufficient for most tablespace DDL statements.

Step 3: Execute the following SQL statement to generate the DDL for the tablespace:

SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'your_tablespace_name') FROM DUAL;

Replace “your_tablespace_name" with the name of the tablespace for which you want to generate the DDL statement.

Get SYSTEM tablespace DDL

Suppose i want DDL for SYSTEM tablespace, then using the below query:

SQL> SET LONG 10000
SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'SYSTEM') FROM DUAL;

Output:

How to get all tablespace DDL

Using the below query you will get the complete DDL of all the tablespaces:

SQL> set lin 300 pagesize 500
SQL> select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;

Output:

If you want semicolon in the end of each DDL genrated, you can enable the SQL TERMINATOR using the below command.

SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

PL/SQL procedure successfully completed.

Output with semicolon:

Uisng the above DDL you can create tablespace with same configuration.

Note: That you will need the necessary privileges to generate the DDL statement for a tablespace. Specifically, you will need the SELECT_CATALOG_ROLE role or the SELECT ANY DICTIONARY system privilege.