Re: Database creation script anybody?

From: Rachel Carmichael <carmichr_at_hotmail.com>
Date: Wed, 03 May 2000 00:48:13 GMT
Message-Id: <10485.104755@fatcity.com>

/* Start of shameless plug

From SQL & PL/SQL Annotated Archives -- Loney & Carmichael end of shameless plug
*/

first script is for Oracle 7.3.x second script is for 8.0

These have NOT been tested for 8i but should work unless you are using the new 8i tablespace creation clauses

Oracle 7

set echo off term on verify off feedback off pagesize 0 select 'Creating tablespace build script...' from DUAL;

accept tablespace_name prompt "Enter the name of the tablespace: " set term off

drop table TSPACE_TEMP;

create table TSPACE_TEMP (

        Lineno      NUMBER,
        Tspace_Name VARCHAR2(30),
        Text        VARCHAR2(500))

declare

cursor TSPACE_CURSOR is

         select Tablespace_Name,
                Initial_Extent,
                Next_Extent,
                Min_Extents,
                Max_Extents,
                Pct_Increase,
                Status,
                Contents
           from DBA_TABLESPACES
          where Tablespace_Name != 'SYSTEM';

   cursor DFILE_CURSOR (C_Tablespace_Name VARCHAR2) is
         select A.Maxextend,
                A.Inc,
                B.File_Name,
                B.File_ID,
                B.Bytes,
                B.Status
           from SYS.FILEXT$    A,
                DBA_DATA_FILES B
          where B.File_ID = A.File#(+)
            and Tablespace_Name = C_Tablespace_Name
          order by File_ID;

   Lv_TS_Tablespace_Name      DBA_TABLESPACES.Tablespace_Name%TYPE;
   Lv_TS_Initial_Extent       DBA_TABLESPACES.Initial_Extent%TYPE;
   Lv_TS_Next_Extent          DBA_TABLESPACES.Next_Extent%TYPE;
   Lv_TS_Min_Extents          DBA_TABLESPACES.Min_Extents%TYPE;
   Lv_TS_Max_Extents          DBA_TABLESPACES.Max_Extents%TYPE;
   Lv_TS_Pct_Increase         DBA_TABLESPACES.Pct_Increase%TYPE;
   Lv_TS_Status               DBA_TABLESPACES.Status%TYPE;
   Lv_TS_Contents             DBA_TABLESPACES.Contents%TYPE;
   Lv_DF_MaxExtend            SYS.FILEXT$.MaxExtend%TYPE;
   Lv_DF_Inc                  SYS.FILEXT$.Inc%TYPE;
   Lv_DF_File_Name            DBA_DATA_FILES.File_Name%TYPE;
   Lv_DF_File_ID              DBA_DATA_FILES.File_ID%TYPE;
   Lv_DF_Bytes                DBA_DATA_FILES.Bytes%TYPE;
   Lv_DF_Status               DBA_DATA_FILES.Status%TYPE;

   Lv_String                  VARCHAR2(800);
   Lv_Lineno                  NUMBER  := 0;
   Lv_DF_Count                NUMBER;
   Lv_Block_Size              NUMBER;

   procedure WRITE_OUT(P_Line INTEGER,  P_Tablespace VARCHAR2,
                       P_String VARCHAR2)

is
begin

      insert into TSPACE_TEMP (Lineno, Tspace_name, Text)
             values (P_Line,P_Tablespace,P_String);

end;

begin

select Value

     into Lv_Block_Size
     from SYS.V_$PARAMETER

where UPPER(Name) = 'DB_BLOCK_SIZE';

open TSPACE_CURSOR;
loop

      fetch TSPACE_CURSOR into Lv_TS_Tablespace_Name,
                               Lv_TS_Initial_Extent,
                               Lv_TS_Next_Extent,
                               Lv_TS_Min_Extents,
                               Lv_TS_Max_Extents,
                               Lv_TS_Pct_Increase,
                               Lv_TS_Status,
                               Lv_TS_Contents;
      exit when TSPACE_CURSOR%NOTFOUND;

      Lv_Lineno := 1;
      Lv_DF_Count := 0;
      Lv_String := 'CREATE TABLESPACE ' || LOWER(Lv_TS_Tablespace_Name);
      WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name, Lv_String);
      Lv_Lineno := Lv_Lineno + 1;

      open DFILE_CURSOR (Lv_TS_Tablespace_Name);

      loop
         fetch DFILE_CURSOR into    Lv_DF_MaxExtend,
                                    Lv_DF_Inc,
                                    Lv_DF_File_Name,
                                    Lv_DF_File_ID,
                                    Lv_DF_Bytes,
                                    Lv_DF_Status;
         exit when DFILE_CURSOR%NOTFOUND;
         Lv_DF_Count := Lv_DF_Count + 1;

         if (Lv_DF_Count > 1)
         then
              Lv_String := '        ,''';
         else
                    Lv_String := 'DATAFILE ''';
         end if;

         Lv_String := Lv_String || Lv_DF_File_Name
                                || ''' SIZE '
                                || (Lv_DF_Bytes)/1024 ||'K' ;

         Lv_String := Lv_String || ' REUSE ';
         WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name, Lv_String);
         Lv_Lineno := Lv_Lineno + 1;

         if (Lv_DF_MaxExtend IS NOT NULL)
         then
              Lv_String := 'AUTOEXTEND ON NEXT '
                              || (Lv_DF_Inc * Lv_Block_Size)/1024
                              || 'K MAXSIZE '
                              || (Lv_DF_MaxExtend * Lv_Block_Size)/1024
                              || 'K';

             WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name, Lv_String);
             Lv_Lineno := Lv_Lineno + 1;
         end if;
      end loop;
      close DFILE_CURSOR;

      Lv_String := 'DEFAULT STORAGE ';
      WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name, Lv_String);
      Lv_Lineno := Lv_Lineno + 1;

      Lv_String := '(';
      WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name, Lv_String);
      Lv_Lineno := Lv_Lineno + 1;
      Lv_String := '   INITIAL    ' || Lv_TS_Initial_Extent/1024 || 'K';
      WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name, Lv_String);
      Lv_Lineno := Lv_Lineno + 1;

      Lv_String := '   NEXT       ' || Lv_TS_Next_Extent/1024 || 'K';
      WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name, Lv_String);
      Lv_Lineno := Lv_Lineno + 1;

      Lv_String := '   MINEXTENTS ' || Lv_TS_Min_Extents;
      WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name, Lv_String);
      Lv_Lineno := Lv_Lineno + 1;

      Lv_String := '   MAXEXTENTS ' || Lv_TS_Max_Extents;
      WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name, Lv_String);
      Lv_Lineno := Lv_Lineno + 1;

      Lv_String := '   PCTINCREASE ' || Lv_TS_Pct_Increase;
      WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name, Lv_String);
      Lv_Lineno := Lv_Lineno + 1;

      Lv_String := ')';
      WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name, Lv_String);
      Lv_Lineno := Lv_Lineno + 1;

      Lv_String := Lv_TS_Status || ' ' || Lv_TS_Contents;
      WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name, Lv_String);
      Lv_Lineno := Lv_Lineno + 1;

      Lv_String := '/';
      WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name, Lv_String);
       Lv_Lineno := Lv_Lineno + 1;

end loop ;
close TSPACE_CURSOR;

end;
/

set trimspool on
spool cre_tbs.sql
select Text
from TSPACE_TEMP
where Tspace_Name like UPPER('&&tablespace_name'); order by Tspace_Name, Lineno
/

spool off

Oracle 8 version