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