| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Reverse engineer tablespaces.
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
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
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).
-- 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 - 16:36:38 CDT
 |
 |