Create Tablespace Script in Oracle 10g

Question

Hi Experts,

I am using the below script for generating a DDL to create tablespaces in the database.

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;

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.

Cheers,

Srinivasan Thirunavukkarasu.

Answer 1

If you're just trying to reverse-engineer an existing tablespace to generate a script, why not just use DBMS_METADATA?

select dbms_metadata.get_ddl('TABLESPACE','yourTablespaceNameOfInterest') 
from dual;

You can generate one of these statements for each tablespace in the database with a simple wrapper if you want them all.