Watch out because this also creates the DDL for the
system tablespace, if you are using real temporary tablespaces it will not
create the DDL for that.
declare
cursor c_ts is select * from dba_tablespaces;
cursor c_df (p_tablespace varchar2) is select * from dba_data_files where tablespace_name = p_tablespace;
s varchar2(2000);
file_name varchar2(1000);
file_size number(10);
begin
dbms_output.enable(100000);
for ts in c_ts loop
dbms_output.put_line('create tablespace ' || ts.tablespace_name || ' datafile ');
for df in c_df(ts.tablespace_name) loop
file_size := ceil(df.bytes/1024/1024);
file_name := df.file_name;
s := s || '''' || file_name || '''' || ' SIZE ' || file_size || 'M, ' || chr(10);
end loop;
s := substr(s, 1, length(s)-3);
dbms_output.put_line(s);
s := 'default storage (' || chr(10) || ' initial ' || ts.initial_extent || chr(10) ||
' next ' || ts.next_extent || chr(10) || ' maxextents unlimited);' || chr(10) || chr(10);
dbms_output.put_line(s);
s := '';
end loop;
exception
when others then
dbms_output.put_line(dbms_utility.format_error_stack);
end;
/
cursor c_ts is select * from dba_tablespaces;
cursor c_df (p_tablespace varchar2) is select * from dba_data_files where tablespace_name = p_tablespace;
s varchar2(2000);
file_name varchar2(1000);
file_size number(10);
begin
dbms_output.enable(100000);
for ts in c_ts loop
dbms_output.put_line('create tablespace ' || ts.tablespace_name || ' datafile ');
for df in c_df(ts.tablespace_name) loop
file_size := ceil(df.bytes/1024/1024);
file_name := df.file_name;
s := s || '''' || file_name || '''' || ' SIZE ' || file_size || 'M, ' || chr(10);
end loop;
s := substr(s, 1, length(s)-3);
dbms_output.put_line(s);
s := 'default storage (' || chr(10) || ' initial ' || ts.initial_extent || chr(10) ||
' next ' || ts.next_extent || chr(10) || ' maxextents unlimited);' || chr(10) || chr(10);
dbms_output.put_line(s);
s := '';
end loop;
exception
when others then
dbms_output.put_line(dbms_utility.format_error_stack);
end;
/
-----Original Message-----
From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 21, 2003 8:49 AM
To: Multiple recipients of list ORACLE-L
Subject: create tablespace scriptHi!
I want to write a "create tablespace" script that creates all "create tablespace" statements for a database.
I got this script working if each tablesspace has only one datafile. But how would I handle it if a tablespace consists of 2 datafiles, e.g. datafile 5 and 87 from dba_data_files...
Is there an id for the datafiles within the tablespace???
Any ideas?
Thanks,
Helmut
