Introduction
In Oracle databases, understanding how to generate DDL of tablespaces is essential for database administrators and developers. The DDL (Data Definition Language) statements allow you to recreate tablespaces in case of migration or disaster recovery scenarios. In this short blog, we’ll explore the syntax and steps to retrieve the DDL of a tablespace in Oracle.
Retrieving DDL of a Tablespace in Oracle
Step 1: Connect to the Database
To begin, ensure you have the necessary privileges to retrieve DDL. Open your preferred SQL client and connect to the Oracle database using appropriate credentials.
[oracle@prod ~]$ . oraenv
ORACLE_SID = [DBPROD] ? DBPROD
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@prod ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 29 14:37:08 2022
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
Step 2: Identify the Tablespace
Identify the name of the tablespace for which you want to obtain the DDL. From v$tablespace data dictionary get details of all tablespaces in the current database.
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
1 SYSAUX YES NO YES 0
0 SYSTEM YES NO YES 0
2 UNDOTBS1 YES NO YES 0
4 USERS YES NO YES 0
3 TEMP NO NO YES 0
Step 3: Retrieve DDL Syntax
Use the following syntax to retrieve the DDL statement for the identified tablespace:
SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE', '<TABLESPACE_NAME>') AS DDL_STATEMENT FROM DUAL;Replace with the name of the tablespace you want to retrieve the DDL for.
- a) Generate Select Query for Generating DDL of each tablespace
- b) Generate DDL of all tablespace in .sql file
- c) Generate DDL of any Specific Tablespace
a) Generate Select Query for Generating DDL of each tablespace
SQL>
set heading off;
set echo off;
set lines 999;
set pages 999
set long 99999
select 'Select dbms_metadata.get_ddl(''TABLESPACE'',''' || Tablespace_name || ''') from dual;' from dba_tablespaces;
Select dbms_metadata.get_ddl('TABLESPACE','SYSTEM') from dual;
Select dbms_metadata.get_ddl('TABLESPACE','SYSAUX') from dual;
Select dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') from dual;
Select dbms_metadata.get_ddl('TABLESPACE','TEMP') from dual;
Select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;Run generated Select statement one by one to obtain the DDL of Tablespace. Example below:
SQL> Select dbms_metadata.get_ddl('TABLESPACE','SYSTEM') from dual;
CREATE TABLESPACE "SYSTEM" DATAFILE
'/u01/app/oracle/oradata/DBPROD/system01.dbf' SIZE 524288000
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT MANUAL
ALTER DATABASE DATAFILE
'/u01/app/oracle/oradata/DBPROD/system01.dbf' RESIZE 985661440
SQL> Select dbms_metadata.get_ddl('TABLESPACE','SYSAUX') from dual;
CREATE TABLESPACE "SYSAUX" DATAFILE
'/u01/app/oracle/oradata/DBPROD/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
ALTER DATABASE DATAFILE
'/u01/app/oracle/oradata/DBPROD/sysaux01.dbf' RESIZE 650117120
SQL> Select dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') from dual;
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/u01/app/oracle/oradata/DBPROD/undotbs01.dbf' SIZE 26214400
AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
ALTER DATABASE DATAFILE
'/u01/app/oracle/oradata/DBPROD/undotbs01.dbf' RESIZE 1111490560
SQL> Select dbms_metadata.get_ddl('TABLESPACE','TEMP') from dual;
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
'/u01/app/oracle/oradata/DBPROD/temp01.dbf' SIZE 38797312
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
SQL> Select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;
CREATE TABLESPACE "USERS" DATAFILE
'/u01/app/oracle/oradata/DBPROD/users01.dbf' SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTOb) Generate DDL of all tablespace in .sql file
SQL>
set heading off;
set echo off;
set lines 999
set pages pages 999
set long 99999
spool ddl_tablespace.sql
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;
CREATE TABLESPACE "SYSTEM" DATAFILE
'/u01/app/oracle/oradata/DBPROD/system01.dbf' SIZE 524288000
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT MANUAL
ALTER DATABASE DATAFILE
'/u01/app/oracle/oradata/DBPROD/system01.dbf' RESIZE 985661440
CREATE TABLESPACE "SYSAUX" DATAFILE
'/u01/app/oracle/oradata/DBPROD/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
ALTER DATABASE DATAFILE
'/u01/app/oracle/oradata/DBPROD/sysaux01.dbf' RESIZE 650117120
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/u01/app/oracle/oradata/DBPROD/undotbs01.dbf' SIZE 26214400
AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
ALTER DATABASE DATAFILE
'/u01/app/oracle/oradata/DBPROD/undotbs01.dbf' RESIZE 1111490560
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
'/u01/app/oracle/oradata/DBPROD/temp01.dbf' SIZE 38797312
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
CREATE TABLESPACE "USERS" DATAFILE
'/u01/app/oracle/oradata/DBPROD/users01.dbf' SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
SQL> spool off;
SQL> exitc) Generate DDL of any Specific Tablespace
After running the SQL query it will ask for the tablespace name and gives the DDL output for the Tablespace mentioned.
SQL>
set heading off;
set echo off;
set lines 999
set pages 999
set long 99999
spool ddl_tablespace.sql
select dbms_metadata.get_ddl('TABLESPACE',UPPER('&tablespace_name')) from dual;
Enter value for tablespace_name: SYSAUX
old 1: select dbms_metadata.get_ddl('TABLESPACE',UPPER('&tablespace_name')) from dual
new 1: select dbms_metadata.get_ddl('TABLESPACE',UPPER('SYSAUX')) from dual
CREATE TABLESPACE "SYSAUX" DATAFILE
'/u01/app/oracle/oradata/DBPROD/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
ALTER DATABASE DATAFILE
'/u01/app/oracle/oradata/DBPROD/sysaux01.dbf' RESIZE 650117120
##OR##
##SELECT DBMS_METADATA.GET_DDL('TABLESPACE', '<TableSpace Name>') AS DDL_STATEMENT FROM DUAL;##
SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'SYSAUX') AS DDL_STATEMENT FROM DUAL;
CREATE TABLESPACE "SYSAUX" DATAFILE
'/u01/app/oracle/oradata/DBPROD/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
ALTER DATABASE DATAFILE
'/u01/app/oracle/oradata/DBPROD/sysaux01.dbf' RESIZE 650117120Conclusion
Retrieving the DDL of a tablespace in Oracle is a straight forward process that can be immensely useful in scenarios where you need to recreate tablespaces during migrations or disaster recovery. By using the DBMS_METADATA.GET_DDL function, you can quickly obtain the necessary DDL statements to recreate the tablespace structure.
Remember to ensure you have the appropriate privileges before attempting to retrieve DDL statements, and always exercise caution when working with critical database objects.
This concludes our quick guide on retrieving the DDL of a tablespace in Oracle. We hope you found this information helpful for your Oracle database management tasks.