title: Create Tablespace Script in Oracle 10g
body: <p>I am using the below script for generating a DDL to create tablespaces in the database.</p>
  
  <pre><code>select 'create tablespace ' || df.tablespace_name || chr(10)
   || ' datafile ''' || df.file_name || ''' size ' || df.bytes 
   || decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize ' 
   || maxbytes) 
   || chr(10) 
   || 'default storage ( initial ' || initial_extent 
   || decode (next_extent, null, null, ' next ' || next_extent )
   || ' minextents ' || min_extents
   || ' maxextents ' ||  decode(max_extents,'2147483645','unlimited',max_extents) 
   || ') ;' "Script To Recreate Tablespaces"
   from dba_data_files df, dba_tablespaces t
   where df.tablespace_name=t.tablespace_name;
  </code></pre>
  
  <p>It works good. But when a tablespace contains two datafiles then also it creates seperate command with create tablespace. Simply it creates two create tablespace commands if a tablespace contains two datafiles. Please share your thoughts.</p>
  
  <p>Cheers,</p>
  
  <p>Srinivasan Thirunavukkarasu.</p>