Skip to main content

Generate DDL Of Tablespaces

 

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 AUTO

b) 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> exit

c) 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 650117120

Conclusion

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.

How To Manage Tablespace in Oracle

Labels:

Comments

Post a Comment

Popular posts from this blog

Upgrade Oracle Database 12c to 19c by DBUA Method

  Introduction Upgrade Oracle Database is a crucial step to ensure you're benefiting from the latest features, performance enhancements, and security updates. In this guide, we'll walk you through the process of upgrading your Oracle Database 12c to the latest version, 19c . Follow these steps to ensure a smooth and successful upgrade Environment Hostname : prod.infoinflux.com Database Name : PROD DB VERSION : 12.2.0.1.0 ORACLE_BASE : /u01/app/oracle DB Home Path : /u01/app/oracle/product/12.2.0.1/db_1 Datafile Location : /u01/app/oracle/oradata/PROD Target DB VERSION : 19.0.0.0 Target DB Path : /u01/app/oracle/product/19c/db_1 Upgrade Method : Database Upgrade Assistant Steps For upgrade using Database Upgrade Assistant (DBUA): Take Latest Database backup Make Directory for 19c Home Download and Unzip Software to 19c Home Directory Install 19c Binary or Software Create Pre and Post Upgrade Scripts Run Pre-Upgrade Script Pre-Upgra...
Post a Comment
Read more

ORA-12012: error on auto execute of job SYS.ORA$AT_OS_OPT_SY_**

  Introduction ORA-12012 error is related to Oracle Database and is part of the Oracle Job Scheduler (DBMS_SCHEDULER) . It usually occurs when an automatic task or job execution encounters an issue. The error message you're seeing, " ORA$AT_OS_OPT_SY_<number> ," is specific to the job causing the problem. During the 12c database creation process, you can see ORA-12012 error in the alert log file when the “ SYS.ORA$AT_OS_OPT_SY_ ” auto job runs. To fix the error, it is necessary to drop the job and recreate it. Errors will be as follows. Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_j000_14524.trc: ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_767" ORA-20001: Statistics Advisor: Invalid task name for the current user ORA-06512: at "SYS.DBMS_STATS", line 47207 ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059 ORA-065...
Post a Comment
Read more

Install Oracle Database 12.2(12c) on Linux

  Introduction Installing Oracle Database 12.2 on Linux system might seem daunting at first, but with the right guidance, it can be a smooth process. In this guide, we'll take you through the step-by-step procedure to install Oracle Database on a Linux environment. By the end of this tutorial, you'll have a functional Oracle Database instance up and running. Steps for Installation: 1. Prerequisites Automatic Setup 2. Prerequisites Manual Setup 3. Install Oracle Database Binary (Software only) 4. Create Database by DBCA 1. Prerequisites Automatic Setup To perform all your prerequisite setup automatically, issue the following command or you can proceed with manual setup explained in 2nd point. [ root @ prod ] # yum install oracle - database - server - 12cR2 - preinstall - y 2. Prerequisites Manual Setup a) Download and Unzip the oracle 12.2 Setup Download or copy the oracle setup file to any location as per your requirement. In...
Post a Comment
Read more