How to generate tablespace ddl in Oracle

How to generate tablespace ddl in Oracle: In this article we will see How to generate tablespace ddl in Oracle for one tablespace and all tablespaces.

Generate one tablespace’s ddl

By this below query you can generate tablespace ddl for a specified tablespace. After run this query it will ask you to provide the tablespace_name.

SQL>
SQL> set heading off;
SQL> set echo off;
SQL> set lines 400 pages 400
SQL> set long 999999
SQL> spool ddl_tablespace.sql
SQL> select dbms_metadata.get_ddl('TABLESPACE',UPPER('&tablespace_name')) from dual;
Enter value for tablespace_name: users
old   1: select dbms_metadata.get_ddl('TABLESPACE',UPPER('&tablespace_name')) from dual
new   1: select dbms_metadata.get_ddl('TABLESPACE',UPPER('users')) from dual


  CREATE TABLESPACE "USERS" DATAFILE
  '/u01/app/oracle/oradata/ORCL/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>

Generate all tablespaces ddl by two process:

Process-1:

By this below query you can generate all tablespace ddl in a spool file.

SQL>
SQL> set heading off;
SQL> set echo off;
SQL> set lines 1000 pages 40000
SQL> set long 999999
SQL> spool ddl_tablespace.sql
SQL> select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;


  CREATE TABLESPACE "SYSTEM" DATAFILE
  '/u01/app/oracle/oradata/ORCL/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/ORCL/system01.dbf' RESIZE 849346560


  CREATE TABLESPACE "SYSAUX" DATAFILE
  '/u01/app/oracle/oradata/ORCL/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/ORCL/sysaux01.dbf' RESIZE 503316480


  CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
  '/u01/app/oracle/oradata/ORCL/undotbs01.dbf' SIZE 26214400
  AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   ALTER DATABASE DATAFILE
  '/u01/app/oracle/oradata/ORCL/undotbs01.dbf' RESIZE 73400320


  CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
  '/u01/app/oracle/oradata/ORCL/temp01.dbf' SIZE 137363456
  AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576


  CREATE TABLESPACE "USERS" DATAFILE
  '/u01/app/oracle/oradata/ORCL/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

Process-2:

By this query we can get the DDL for all tablespaces:

SQL>
SQL> set heading off;
SQL> set echo off;
SQL> set lines 1000;
SQL> set pages 40000
SQL> set long 999999
SQL> 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;

SQL>

After getting output you need to run this queries to get ddl for tablespace which you needed. example is given below,

SQL>
SQL> select dbms_metadata.get_ddl('TABLESPACE','SYSTEM') from dual;


  CREATE TABLESPACE "SYSTEM" DATAFILE
  '/u01/app/oracle/oradata/ORCL/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/ORCL/system01.dbf' RESIZE 849346560


SQL>
SQL> select dbms_metadata.get_ddl('TABLESPACE','SYSAUX') from dual;


  CREATE TABLESPACE "SYSAUX" DATAFILE
  '/u01/app/oracle/oradata/ORCL/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/ORCL/sysaux01.dbf' RESIZE 503316480


SQL>
SQL> select dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') from dual;


  CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
  '/u01/app/oracle/oradata/ORCL/undotbs01.dbf' SIZE 26214400
  AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   ALTER DATABASE DATAFILE
  '/u01/app/oracle/oradata/ORCL/undotbs01.dbf' RESIZE 73400320


SQL>
SQL> select dbms_metadata.get_ddl('TABLESPACE','TEMP') from dual;


  CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
  '/u01/app/oracle/oradata/ORCL/temp01.dbf' SIZE 137363456
  AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576


SQL>
SQL> select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;


  CREATE TABLESPACE "USERS" DATAFILE
  '/u01/app/oracle/oradata/ORCL/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>

Retrieving Metadata :

If you are retrieving metadata, you can specify:

• The kind of object to be retrieved. This can be either a particular object type (such as a table, index, or procedure) or a heterogeneous collection of object types that form a logical unit (such as a database export or schema export).
• Optional selection criteria, such as owner or name.
• Parse items (attributes of the returned objects to be parsed and returned separately).

How to generate user or schema ddl in Oracle

How to generate table ddl in Oracle

I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.