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.
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.