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" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>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" <[EMAIL PROTECTED]>
>>Reply-To: [EMAIL PROTECTED]
>>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>>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
>>[EMAIL PROTECTED] Quad/Tech International, Sussex, WI
>>USA
>>--
>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>--
>>Author: Jesse, Rich
>> 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).
_________________________________________________________________
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).
