Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Temp tablespace
Morning,
you don't need to worry about it in Oracle UNLESS your temporary tablespace isn't actually temporary :o)
Login as system and do this :
select username, default_tablespace, temporary_tablespace, contents
from dba_users, dba_tablespaces where dba_tablespaces.tablespace_name =dba_users.temporary_tablespace
Any user which has a temporary tablespace which is not actually temporary needs to be altered to give it a new temporary tablespace which *is* temporary !
alter user SYS temporary_tablespace TEMP;
Any user which has SYSTEM as it's default tablespace needs to be moved out pronto (exceptions noted above) :
alter user bad_boy default_tablespace USERS;
By getting your users into a real temporary tablespace you will actually see a performance increase as there are no permanent objects being created and dropped as the day goes by.
Now, when you shut down the database, the temporary tablespace will be cleaned out ready for next time.
HTH
Cheers,
Norman.
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: aprinsloo_at_websoft.co.za (Allen) [mailto:aprinsloo_at_websoft.co.za]
Posted At: Friday, March 28, 2003 11:02 AM
Posted To: server
Conversation: Temp tablespace
Subject: Temp tablespace
Hi,
Due to my limited Oracle experience this question might seem a bit basic, but I'd appreciate any help.
Product: Oracle 8.1.6
Platform: MS Server 2000
How do I clean out the space used by the temp table space on our Oracle database server. The temp table space is of type "tempory" but keeps filling up and growing, is there a way to automate the clean out of it or do I need to manually do something to it? Previous dbms's I've worked on Sybase and MS Sql Server cleaned out tempory db work space after every commited transaction, this does not seem to be the case with Oracle... or am I msitaken?
Thx in advance
Allen
Received on Fri Mar 28 2003 - 06:35:33 CST