Comments embedded and below
Hth
--
Sybrand Bakker
Senior Oracle DBA
to reply remove '-verwijderdit' from my e-mail address
"Edzard" <edzard_at_volcanomail.com> wrote in message
news:5d75e934.0207151039.59ba72a8_at_posting.google.com...
> Hello,
>
> I'm working with a Oracle 7.3.4.5 database on Unix. The total database
> size is about 8G. There are 40 concurrent users on average. A
> relatively large part of the storage capacity is taken by the
> auxillary tablepaces TEMP and RBS. The temporary tablespace is 1G.
> This is only rarely used for a single report. The tablespace for
> rollback segments is also 1G, also rarely used, when populating a
> large index-organized table.
>
> To make thing easy, disk storage is allocated in parts of 512M (raw
> logical volumes). Now I run out of free raw logical volumes. I need
> one to extend the data tablespace. I might create a new one but there
> is a limitation of the backup capacity and I'd prefer to stick to the
> existing raw logical volumes.
>
This is not making things easy, this is just lazy administration.
> So it occured to me that a lot of space is wasted with the TEMP and
> RBS tablespaces and I wondered if I could not combine temporary and
> rollback segments in a single tablespace. I figure that the combined
> tablespace could do with three 512M data files, that is one less than
> the two seperate tablespaces do have now.
>
> Has anyone a opinion about this plan?
bad idea (tm)
Disregarding the explicit Oracle advice not to do this.
Both segmenttypes will potentially grow indefinitely.
> The script to create the new combined rollback/temporary tablespace is
> below (not tested yet). To prevent fragmentation I chooses a uniform
> extent size for both types of segments. My main worry is that the
> temporary segments behave different when created in a PERMANENT type
> of tablespace.
>
> Thanks,
> Edzard Pasma
>
>
> CREATE TABLESPACE rbt
> DATAFILE '/u11/oracle/p1/oradata/rbt01.ln' SIZE 524272K REUSE
> DEFAULT STORAGE (INITIAL 4M NEXT 4M PCTINCREASE 0)
> MAXEXTENTS 256;
>
> ALTER TABLESPACE rbt
> ADD DATAFILE '/u11/oracle/p1/oradata/rbt02.ln' SIZE 524272K REUSE;
> ALTER TABLESPACE rbt
> ADD DATAFILE '/u11/oracle/p1/oradata/rbt03.ln' SIZE 524272K REUSE;
>
> CREATE PUBLIC ROLLBACK SEGMENT rbs001 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs002 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs003 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs004 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs005 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs006 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs007 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs008 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs009 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs010 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs011 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs012 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs013 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs014 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs015 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
> CREATE PUBLIC ROLLBACK SEGMENT rbs016 TABLESPACE rbt STORAGE (OPTIMAL
> 12M );
>
> ALTER ROLLBACK SEGMENT rbs01 ONLINE;
> ALTER ROLLBACK SEGMENT rbs02 ONLINE;
> ALTER ROLLBACK SEGMENT rbs03 ONLINE;
> ALTER ROLLBACK SEGMENT rbs04 ONLINE;
> ALTER ROLLBACK SEGMENT rbs05 ONLINE;
> ALTER ROLLBACK SEGMENT rbs06 ONLINE;
> ALTER ROLLBACK SEGMENT rbs07 ONLINE;
> ALTER ROLLBACK SEGMENT rbs08 ONLINE;
> ALTER ROLLBACK SEGMENT rbs09 ONLINE;
> ALTER ROLLBACK SEGMENT rbs10 ONLINE;
> ALTER ROLLBACK SEGMENT rbs11 ONLINE;
> ALTER ROLLBACK SEGMENT rbs12 ONLINE;
> ALTER ROLLBACK SEGMENT rbs13 ONLINE;
> ALTER ROLLBACK SEGMENT rbs14 ONLINE;
> ALTER ROLLBACK SEGMENT rbs15 ONLINE;
> ALTER ROLLBACK SEGMENT rbs16 ONLINE;
>
> ALTER USER sys TEMPORARY TABLESPACE rbt;
> ALTER USER system TEMPORARY TABLESPACE rbt;
> ALTER USER rapp TEMPORARY TABLESPACE rbt;
> ALTER USER raph TEMPORARY TABLESPACE rbt;
>
> SPOOL OFF
According to the rule of 4 you only need 10 rollback segments, not 16.
Also using OPTIMAL is bad practice as it will increase the change you are
hitting ora-1555
Received on Mon Jul 15 2002 - 14:34:57 CDT
