Child pages
  • Generate Create Tablespace DDL
Skip to end of metadata
Go to start of metadata

Generate SQL to recreate existing tablespaces

spool create_tbs.lst
set serveroutput on size 1000000
DECLARE
 CURSOR get_ts IS SELECT * FROM dba_tablespaces
                  WHERE tablespace_name != 'SYSTEM';
 CURSOR get_df (p_ts VARCHAR2) IS
                  SELECT * from dba_data_files
                  WHERE tablespace_name = p_ts;
 l_str VARCHAR2(10);
BEGIN
FOR ts_rec IN get_ts LOOP
 dbms_output.put_line ('CREATE TABLESPACE '||ts_rec.tablespace_name);
 FOR df_rec IN get_df (ts_rec.tablespace_name) LOOP
   IF get_df%ROWCOUNT = 1 THEN
      l_str := 'DATAFILE';
   ELSE
      l_str := ',';
   END IF;
   dbms_output.put_line (l_str||' '
               ||chr(39)||df_rec.file_name||chr(39)
               ||' SIZE '||df_rec.bytes||' REUSE ');
   if df_rec.autoextensible = 'YES' then
      dbms_output.put_line (' AUTOEXTEND ON'
                            ||' NEXT '||df_rec.increment_by );
      if df_rec.maxbytes = 68719443968 then
         dbms_output.put_line (' MAXSIZE UNLIMITED');
      else
         dbms_output.put_line (' MAXSIZE '||df_rec.maxbytes);
      end if;
   end if;
 END LOOP;
  dbms_output.put_line  ('EXTENT MANAGEMENT ' ||ts_rec.extent_management );
  if ts_rec.extent_management = 'LOCAL' then
     if ts_rec.allocation_type = 'SYSTEM' then
        dbms_output.put_line (' AUTOALLOCATE ');
     else
        dbms_output.put_line (' UNIFORM SIZE '||ts_rec.initial_extent);
     end if; 
  end if;
  if ts_rec.extent_management = 'DICTIONARY' then
    dbms_output.put_line ('DEFAULT STORAGE (INITIAL '||ts_rec.initial_extent
       ||' NEXT '||ts_rec.next_extent
       ||' MINEXTENTS '||ts_rec.min_extents
       ||' MAXEXTENTS '||ts_rec.max_extents
       ||' PCTINCREASE '||ts_rec.pct_increase||' ) ');
  end if;
  dbms_output.put_line  (' ONLINE;');
  dbms_output.new_line;
END LOOP;
END;
/ 
spool off