Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE-ENGINEERING OF TABLESPACES

RE: RE-ENGINEERING OF TABLESPACES

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Tue, 29 May 2001 11:54:30 -0700
Message-ID: <F001.00312606.20010529112523@fatcity.com> 






I made this script available on my site with due credit to the author.
I made a small modification on the set variables so they will be read by
PL/SQL developer and other non-sql plus clients.


(I actually already copied it up there, but didn't know who wrote it so I

had it as Anon).




"Walking on water and developing software from a specification are easy if
both are frozen."



Christopher R. Spence


Oracle DBA


Fuelspot 





-----Original Message-----


Sent: Thursday, May 24, 2001 3:58 PM


To: Multiple recipients of list ORACLE-L




Hi Listers ,


I saw some posts about re-engineering of tablespace
creation script . This is Rachel's script which I
Modified to include LMT's . If anybody is wondering ,
I am posting this with her permission ..;) Any
'stirring' living being in ORACLE universe dare not
risk the wrath of 'Goddess'...( Alright Rachel...Am I
glad you don't know where I live ...! )



SCRIPT STARTS HERE .....





set echo off term on verify off feedback off pagesize
0 


select 'GENERATING TABLESPACE BUILD SCRIPTS...' from
DUAL;



select 'TEMPORARY LMTs NOT GENERATED ....' From dual ;



set term off


DROP TABLE TSPACE_TEMP ;



create global temporary table TSPACE_TEMP (

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




ON COMMIT delete rows 


/



declare


   cursor TSPACE_CURSOR is

         select Tablespace_Name,
                Initial_Extent,
                Next_Extent,
                Min_Extents,
                Max_Extents,
                Pct_Increase,
                Min_Extlen,
                Status,
                Contents,
                    extent_management,
                    allocation_type ,
                Logging
           from DBA_TABLESPACES
          where Tablespace_Name !='SYSTEM' and 
            max_extents is not null ;

   cursor DFILE_CURSOR (C_Tablespace_Name VARCHAR2) is
         select Maxbytes,
                Increment_By,
                File_Name,
                Bytes,
                Status,
                Autoextensible
           from DBA_DATA_FILES 
          where 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_Min_Extlen          
DBA_TABLESPACES.Min_Extlen%TYPE;
   Lv_TS_Status              
DBA_TABLESPACES.Status%TYPE;
   Lv_TS_Contents            




DBA_TABLESPACES.Contents%TYPE;


   Lv_Ts_extent_manage  


DBA_TABLESPACES.extent_management%TYPE;


   Lv_Ts_allocation_type


DBA_TABLESPACES.allocation_type%TYPE;


   Lv_TS_Logging             


DBA_TABLESPACES.Logging%TYPE;


   Lv_DF_Maxbytes            
DBA_DATA_FILES.Maxbytes%TYPE;
   Lv_DF_Increment_By        
DBA_DATA_FILES.Increment_By%TYPE;
   Lv_DF_File_Name           
DBA_DATA_FILES.File_Name%TYPE;
   Lv_DF_Bytes               
DBA_DATA_FILES.Bytes%TYPE;
   Lv_DF_Status              
DBA_DATA_FILES.Status%TYPE;
   Lv_DF_Autoextensible      
DBA_DATA_FILES.Autoextensible%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_Min_Extlen,
                               Lv_TS_Status,
                                         Lv_TS_Contents,
                                         Lv_Ts_extent_manage,
                                         Lv_Ts_allocation_type,
                               Lv_TS_Logging;
      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_Maxbytes,
                                   
Lv_DF_Increment_By,
                                    Lv_DF_File_Name,
                                    Lv_DF_Bytes,
                                    Lv_DF_Status,
                                   
Lv_DF_Autoextensible;
         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_Autoextensible = 'YES')
         then
              Lv_String := 'AUTOEXTEND ON NEXT '
                            || (Lv_DF_Increment_By *
Lv_Block_Size)/1024
                                     || 'K MAXSIZE '
                                     ||


(Lv_DF_Maxbytes/1024)

                                     || 'K';

             WRITE_OUT(Lv_Lineno,
Lv_TS_Tablespace_Name, Lv_String);
             Lv_Lineno := Lv_Lineno + 1;
         end if;
      end loop;
      close DFILE_CURSOR;
        
        if ( Lv_Ts_extent_manage = 'DICTIONARY' )
        THEN
      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;
      WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name,
Lv_String);
      Lv_Lineno := Lv_Lineno + 1;

      Lv_String := '   NEXT       ' ||
Lv_TS_Next_Extent; 
      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;
        
      if (Lv_TS_Min_Extlen > 0)
      then
          Lv_String := 'MINIMUM EXTENT
'||Lv_TS_Min_Extlen||' ';
      else
          Lv_String := '';
      end if;

      Lv_String := Lv_String || Lv_TS_Status || ' ' 
                    || Lv_TS_Contents || ' ' ||
Lv_TS_Logging;
      WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name,
Lv_String);
      Lv_Lineno := Lv_Lineno + 1;
        ELSE
        Lv_String := 'EXTENT MANAGEMENT LOCAL ' ||
Lv_TS_allocation_type ||
        ' SIZE '||Lv_TS_Initial_Extent;
      WRITE_OUT(Lv_Lineno, Lv_TS_Tablespace_Name,
Lv_String);
      Lv_Lineno := Lv_Lineno + 1;
        End if;

      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


 order by Tspace_Name, Lineno


/


spool off


set term on 


prompt OUTPUT SPOOLED TO cre_tbs.sql




END OF SCRIPT.....






Regards,


RS






Do You Yahoo!?


Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sakthi , Raj
  INET: rajan_sakthi_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: cspence_at_FuelSpot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).


Received on Tue May 29 2001 - 13:54:30 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US