wanna bet? goddesses know much
but yea, any time I post something that someone else can fix or if I post
something wrong, just go ahead and post the corrections to the list. Unlike
you guys, *I* don't believe I'm a goddess :)
>From: "Sakthi , Raj" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE-ENGINEERING OF TABLESPACES
>Date: Thu, 24 May 2001 11:57:53 -0800
>
>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: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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).
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
