SQL Script Generate Create Tablespaces

Updated : 18-Mar-2002
Version : 2.0

Description

This script generates a create tablespace script from an existing database. It is especially useful of you need a duplicate database on a different machine and have a large number of tablespaces. The database will then be ready for a full database import.

Parameters

None.

SQL Source

spool gents.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 each tablespace loop through the datafiles
  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;

/* Extent Management Clause */

   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

Previous Oracle Version Links

Generate Create Tablespaces

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal