Extracting Tablespace DDLs in Oracle Database

When creating a new Oracle database similar to an existing one, you might need to replicate the tablespaces from the original database. Instead of manually recreating tablespaces, Oracle provides a simple way to extract the Data Definition Language (DDL) statements for tablespaces using the DBMS_METADATA.GET_DDL function.

This guide shows you how to generate the tablespace DDLs from an existing Oracle database, which can then be used to recreate them in a new environment.

Steps to Generate Tablespace DDLs

Set SQL*Plus environment settings: Adjust the settings to handle large outputs and ensure proper formatting.
```
SQL> SET PAGES 999;
SQL> SET LONG 90000;
```
Enable spooling: Use the SPOOL command to write the output to a file.
```
SQL> SPOOL ddl_list.sql;
```

Execute the query: Run the query to extract tablespace DDLs.

SQL> SELECT dbms_metadata.get_ddl('TABLESPACE', tb.tablespace_name) 
     FROM dba_tablespaces tb;

Stop spooling: End the spooling process to save the file.
```
SQL> SPOOL OFF;
```

The ddl_list.sql file will contain the DDL statements for all tablespaces in the database. You can use this file to recreate the tablespaces in the target database.

Sample Output

The query generates the following type of DDL statements:

CREATE TABLESPACE "USERS" DATAFILE
  '/u02/oracle/oradata/ORCL/datafiles/users01.dbf' SIZE 5242880
  AUTOEXTEND ON NEXT 52428800 MAXSIZE 20000M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
  NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE "TOOLS" DATAFILE
  '/u02/oracle/oradata/ORCL/datafiles/tools01.dbf' SIZE 67108864
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
  NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE "INDX" DATAFILE
  '/u02/oracle/oradata/ORCL/datafiles/indx01.dbf' SIZE 268435456
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
  NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

Understanding the Output

Each tablespace DDL includes the following key components:

Tablespace Name: The name of the tablespace (e.g., USERS, TOOLS, INDX).
Datafile Path: The location and size of the tablespace’s datafiles.
Autoextend Settings: Controls how the datafile can grow (e.g., AUTOEXTEND ON).
Extent Management: Specifies whether the tablespace uses LOCAL extent management and allocation policies.
Segment Space Management: Defines segment space settings such as AUTO.

Additional Tips

Filter specific tablespaces: To extract DDL for a specific tablespace, modify the query with a WHERE clause:

SELECT dbms_metadata.get_ddl('TABLESPACE', tb.tablespace_name) 
FROM dba_tablespaces tb 
WHERE tb.tablespace_name = 'USERS';

Verify tablespace paths: Ensure the paths in the DDL match the directory structure of the target system.
Backup existing database: Always back up your database before making structural changes.

Conclusion

Extracting tablespace DDLs using DBMS_METADATA.GET_DDL is a quick and efficient way to replicate database structures. Whether you’re migrating to a new environment or creating a backup, this method simplifies the process by generating precise and ready-to-use DDL statements.

By following the steps outlined above, you can easily recreate tablespaces in your target Oracle database.