Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Can I combine TEMP and RBS tablespace?

Re: Can I combine TEMP and RBS tablespace?

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Mon, 15 Jul 2002 19:50:14 GMT
Message-ID: <3D33273F.D21A6F55@exesolutions.com>


Edzard wrote:

> 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.
>
> 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?
>
> 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
Even if is technically possible, which I would doubt, have you checked into the price of hard disks lately? Another drive will cost you about as much as you spend at a decent restaurant for dinner. If space is an issue ... buy more.

Daniel Morgan Received on Mon Jul 15 2002 - 14:50:14 CDT

Original text of this message

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