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