Can I combine TEMP and RBS tablespace?

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 Received on Mon Jul 15 2002 - 13:39:49 CDT