rem Name:    dumpts.sql
rem Purpose: Create script which recreates tablespaces
rem Usage:   @dumpts <%tablespace%> <search> <replace>
rem Subject: space
rem Attrib:  sql dba
rem Notes:   Known bug--does not handle tablespaces with multiple
rem              data files correctly

@setup
@output

define ts="upper('&&1')"
define srch="'&&2'"
define repl="'&&3'"

spool dumpts.tmp

declare
  last_ts varchar2(30) := null;
  text varchar2(240);
  i number;
begin
for r in (
  select f.*,t.contents
  from sys.dba_data_files f, sys.dba_tablespaces t
  where f.tablespace_name = t.tablespace_name
  and f.tablespace_name like &&ts
  and f.tablespace_name <> 'SYSTEM'
  order by f.tablespace_name,f.file_id
) loop
  if last_ts is null or last_ts<>r.tablespace_name then
    if last_ts is not null then
      sys.dbms_output.put_line(';');
    end if;
    sys.dbms_output.put_line('prompt Creating '||r.tablespace_name||'...');
    sys.dbms_output.put_line('create tablespace '||r.tablespace_name);
    last_ts:=r.tablespace_name;
    i:=1;
  end if;
  text := 'datafile '''||replace(r.file_name,&&srch,&&repl)||''' size '||
    to_char(r.bytes/(1024*1024))||'M reuse' ;
  if i > 1 then
    text := ','||text;
  end if;
  sys.dbms_output.put_line(text);
  sys.dbms_output.put_line(r.contents);
end loop;
  sys.dbms_output.put_line(';');
end;
/
spool off

undef ts srch repl

@setdefs

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips