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 -> Temp tablespace

Temp tablespace

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Fri, 28 Mar 2003 12:35:33 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA7037C63C6@lnewton.leeds.lfs.co.uk>


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
  1. Make sure no-one is using SYSTEM as their default tablespace - except SYS, OUTLN and DBSNMP (if you have those users).
  2. Make sure no-one is using SYSTEM as their temporary tablespace - with no exceptions.
  3. Make sure that all the temporary_tablespaces are flagged up as temporary in the first query.

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.



Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk
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

Original text of this message

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