Tip

Tablespace generation 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;

DECLARE
  
  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;
    
BEGIN

  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);
    
   Loop
   
     fetch cur_Ts_files into cur_ts_files_rec;  
     Exit when cur_ts_files%notfound; 
   
   IF count_files >=

    Requires Free Membership to View

1 THEN create_ts_string:=create_ts_string|| CHR(10) ||' '''||cur_ts_files_rec.file_name||''''|| ' SIZE '||cur_ts_files_rec.bytes/1024||' K '||' , '; ELSE create_ts_string:=create_ts_string||''''||cur_ts_files_rec.file_name||''''|| ' 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||' ;' ; ELSE Create_Ts_String := Create_Ts_String||CHR(10)||'EXTENT MANAGEMENT LOCAL AUTOALLOCATE '||' ;' ; END IF; ELSE 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 ; '; END IF; --DBMS_Output.Put_Line(Create_Ts_String); counter := counter + 1; Insert into ts_gen_scripts values(counter, Create_Ts_String); End Loop; commit; Exception When Others Then Raise_Application_Error(-20999,'Something Wrong..'||SQLERRM); END; / spool ts_gen_scripts.sql; prompt spool err.log; select text from ts_gen_scripts order by line; prompt spool off; prompt 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 first published in November 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.