DDL to recreate all your tablespaces

Home

Toolkit:

My OraFAQ Blog

Contact me

Use this DDL to generate a script to recreate all your permanent tablespaces. Limitations:

This script is also useful when you want to copy a database from one server to another one. A full export/import will reorganize all the tablespaces, so moving, copying, or recreating a database is a great opportunity in terms of space management. There's a problem, though: if the disks on the new server are set up differently from the source server, the full import will fail to create the necessary tablespaces.

Solution: Run the script below on the production database, edit the datafile names in the output, and run the resulting DDL on your empty target database before running the full import. The "create tablespace" statements in the export file will fail harmlessly and all data will be imported to its new home. 


 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) 
 || ') ;'
 from dba_data_files df, dba_tablespaces t
 where df.tablespace_name=t.tablespace_name 
/
Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...