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: Reverse engineer tablespaces.

Re: Reverse engineer tablespaces.

From: Rachel Carmichael <carmichr_at_hotmail.com>
Date: Wed, 23 May 2001 19:43:44 -0700
Message-ID: <F001.0030CD22.20010523191559@fatcity.com> 






okay, I got asked this offline and it makes sense to answer here.



This does NOT work for tempfiles or LMTs. It was written when Oracle was at 
8.0.5 and neither of those constructs existed.



However, since the request was to convert 8.0.6 to 8.1.7, it should work 
properly.




>From: "Rachel Carmichael" <carmichr_at_hotmail.com>

>Reply-To: ORACLE-L_at_fatcity.com

>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

>Subject: Re: Reverse engineer tablespaces.

>Date: Wed, 23 May 2001 12:11:46 -0800

>

>from the Annotated Archives:  (enter % when asked for the name of the

>tablespace and it will generate all of them)

>

>

>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,

>                Min_Extlen,

>                Status,

>                Contents,

>                Logging

>           from DBA_TABLESPACES

>          where Tablespace_Name !='SYSTEM';

>

>   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_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_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;

>

>      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;

>

>      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_tbs8.sql

>select Text

>  from TSPACE_TEMP

>order by Tspace_Name, Lineno

>/

>spool off

>

>

>

>>From: "Jesse, Rich" <Rich.Jesse_at_qtiworld.com>

>>Reply-To: ORACLE-L_at_fatcity.com

>>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

>>Subject: Reverse engineer tablespaces.

>>Date: Wed, 23 May 2001 11:01:26 -0800

>>

>>So, I need to re-create a database to prepare for migration.  The current

>>DB

>>is 8.0.6, and I need to create an 8.1.7 copy.  Does anyone have a script 

>>to

>>reverse engineer the CREATE TABLESPACE commands?  I started working on 

>>one,

>>but surely I can't be the first to invent the wheel.

>>

>>TIA,


>>Rich Jesse                          System/Database Administrator

>>Rich.Jesse_at_qtiworld.com             Quad/Tech International, Sussex, WI 

>>USA


>>--

>>Please see the official ORACLE-L FAQ: http://www.orafaq.com

>>--

>>Author: Jesse, Rich

>>   INET: Rich.Jesse_at_qtiworld.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).

>

>_________________________________________________________________

>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: carmichr_at_hotmail.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).






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: carmichr_at_hotmail.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 Wed May 23 2001 - 21:43:44 CDT












Original text of this message













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