set echo off
set termout on

--
--  File     : cr_ddl_ts8x.sql
--  Version  : 1.0 / 31.08.2000
--  Created  : Hans Wesnitzer / MuniQSoft GmbH
--
--  Purpose  : Extract DDL statements to restore all defined tablespaces
--             except SYSTEM.
--
--  Requires : Oracle RDBMS 8 onwards
--
--  To Start : Must be called as user SYSTEM from sqlplus.
--
--  Changes  :
--

set feedback off
set serveroutput on
set termout on
set trimspool on

declare

  cursor c_ts is
    select *
    from dba_tablespaces
    where tablespace_name not in ('SYSTEM');

  cursor c_df (i_ts varchar2) is
    select *
    from dba_data_files
    where tablespace_name = i_ts
    order by file_id;

  first boolean default true;
  cnt integer default 0;

  text varchar2(255) default '';  -- 255 bytes is limit of dbms_output.put_line

  procedure pl (line in varchar2) is
  begin
    dbms_output.put_line(line);
  end;

begin

  dbms_output.enable(1000000);

  for rec_ts in c_ts loop

   first := true;
   cnt := 0;

   pl('-- ');
   pl('-- Generate DDL Statements for TABLESPACE ' || rec_ts.tablespace_name);
   pl('-- Datum: ' || to_char(sysdate, 'DD.MM.RRRR HH24:MI:SS'));
   pl('-- ');
   pl(' ');
   pl('CREATE TABLESPACE ' || rec_ts.tablespace_name);
   pl('  DATAFILE ');

   for rec_df in c_df(rec_ts.tablespace_name) loop

     cnt := cnt + 1;
     if first = true then
       first := false;
     else
       pl (text || ',');
     end if;

     text := '    ''' || rec_df.file_name || ''' SIZE ' || rec_df.bytes;

-- feature ab 8.x
     if rec_df.autoextensible = 'YES' then
       text := text || ' AUTOEXEND ON NEXT ' || rec_df.increment_by || ' MAXSIZE ' || rec_df.maxbytes;
     end if;

   end loop;
   pl(text);

   pl('    DEFAULT STORAGE');
   pl('    (');
   pl('      INITIAL '     || rec_ts.initial_extent);
   pl('      NEXT '        || rec_ts.next_extent);
   pl('      MINEXTENTS '  || rec_ts.min_extents);
   pl('      MAXEXTENTS '  || rec_ts.max_extents);
   pl('      PCTINCREASE ' || rec_ts.pct_increase);
   pl('    )');

   pl('    ' || rec_ts.status);
   pl('    ' || rec_ts.contents);
   pl('/');
   pl(' ');

 end loop;

end;

/