Problem solve Get help with specific problems with your technologies, process and projects.

Tablespace generation script

This script can be used to generate a CREATE TABLESPACE script.

This script can be used to generate a CREATE TABLESPACE script. It will be very useful in environments where it...

is required to recreate databases or simulate the existing database setup in a different environment. Just run this script and you will get the entire tablespace creation script -- fire it up in the newly setup box.

You can also keep this generated code as a safety measure. If you want to recreate certain tablespaces (in case of tablespace or system failure), this will come in very handy. This code should work in any of 7.3 or 8.x Oracle database.

-- Author - Arul kumar
-- Date   - 11-Oct-2002
-- This script can be used to generate Create Tablespace Script.

set pages 0;
set head off;

Create table ts_gen_scripts(line int, text varchar2(2000));

Truncate table ts_gen_scripts;

  cursor cur_ts is select * from dba_Tablespaces where tablespace_name !='SYSTEM';
  cursor cur_ts_files (ts_name varchar2) is select * from dba_data_files where  tablespace_name=ts_name 
 order by tablespace_name, file_id;
  cur_ts_files_rec cur_ts_files%rowtype;
  create_ts_string varchar2(2000);

  counter int := 0; 
  count_files int := 0;

  for cur_ts_rec in cur_ts loop
  create_ts_string:='CREATE TABLESPACE '||cur_ts_rec.tablespace_name||CHR(10)||'DATAFILE ';
   open cur_ts_files(cur_ts_rec.tablespace_name);
     fetch cur_Ts_files into cur_ts_files_rec;  
     Exit when cur_ts_files%notfound; 
   IF count_files >= 1 THEN

     create_ts_string:=create_ts_string|| CHR(10) ||'         '''||cur_ts_files_rec.file_name||''''||
        ' SIZE '||cur_ts_files_rec.bytes/1024||' K '||' , ';
       ' SIZE '||cur_ts_files_rec.bytes/1024||' K '||' , ';
   END IF;
   count_files := count_files + 1 ;
    End Loop;
   count_files := 0;
   Close Cur_TS_Files;
  Create_Ts_String := RTRIM(Create_Ts_String,' , '); -- trim the trailing ',' 
  -- attach other parameters

  -- extent management  ? local / dictionary
  -- if locaL -  ? UNIFORM / AUTOALLOCATE      

  IF Cur_Ts_Rec.extent_management = 'LOCAL' THEN
   IF Cur_Ts_Rec.Allocation_Type = 'UNIFORM' THEN
     Create_Ts_String := Create_Ts_String||CHR(10)||'EXTENT MANAGEMENT LOCAL UNIFORM SIZE '||Cur_Ts_Rec.Initial_Extent||' ;' ;
     Create_Ts_String := Create_Ts_String||CHR(10)||'EXTENT MANAGEMENT LOCAL AUTOALLOCATE '||' ;' ;

   Create_Ts_String := Create_Ts_String|| CHR(10) ||' DEFAULT STORAGE ( '||CHR(10) ||
         ' INITIAL       '||Cur_Ts_Rec.Initial_Extent  || CHR(10) ||
        ' NEXT          '||Cur_Ts_Rec.Next_Extent   || CHR(10) ||
        ' MINEXTENTS    '||Cur_Ts_Rec.Min_Extents   || CHR(10) ||
        ' PCTINCREASE   '||Cur_Ts_Rec.Pct_Increase||' ) ;' ;
 END IF;                 
 IF Cur_Ts_Rec.contents = 'TEMPORARY' THEN
  Create_Ts_String := RTRIM ( Create_Ts_String, ';');
  Create_Ts_String := Create_Ts_String||' TEMPORARY ; ';

 counter := counter + 1;

 Insert into ts_gen_scripts values(counter, Create_Ts_String);
  End Loop;


When Others Then

Raise_Application_Error(-20999,'Something Wrong..'||SQLERRM);

spool ts_gen_scripts.sql;
prompt spool err.log;
select text from ts_gen_scripts order by line;
prompt spool off;
spool off;

prompt chk in ts_gen_scripts.sql !

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was last published in November 2002

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.