Oracle-L: RE: Oracle TEMPORARY SEGMENT

From: Zabair Ahmed <roon987_at_yahoo.co.uk>
Date: Tue, 11 Feb 2003 08:19:17 -0800
Message-ID: <F001.0054958B.20030211081917@fatcity.com>

Hi Jerry, I tried your suggestion of altering the pctincrease to a non-zero value, although the original temp segment has disappeared from the dba_segments. I now have a different temp segment which is lingering around in dba_segments. Zabair
Whittle Jerome Contr NCI <Jerome.Whittle_at_scott.af.mil> wrote: Hi,

This is what I use to clean up a temp tablespace. The tablespace is named 'temp' and it is normally set to PCTINCREASE of 10. You will need to insert the correct values for your temp tablespace. Altering the tablespace a little wakes us SMON which will clean things up if it can.

alter tablespace temp default storage(pctincrease 0);

alter tablespace temp default storage(pctincrease 10);

50Mb seems rather small for a temp tablespace.

Jerry Whittle

ASIFICS DBA NCI Information Systems Inc.

jerome.whittle_at_scott.af.mil

618-622-4145

-----Original Message-----

Is their any way of identifying which user/process is holding onto a temporary segment. I've got a 50Mb temporary tablespace and there is a temporary segment which is 47Mb which is not being freed up by SMON.

And, if i can't identify who is holding onto this TEMP segment, is their a way in which I can delete it and stop the alert log being flooded with ORA-1652.

As I say the SMON is failing to clean up this TEMP segment and it's been on-going for a number of days and I don't have the option of bouncing the database.

Oracle 8.1.7.3 on HP-UX 11.

TIA

With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs <http://uk.yahoo.com/mail/tagline_xtra/?http://uk.docs.yahoo.com/mail_storage.html>

With Yahoo! Mail you can get a bigger mailbox -- choose a size that fits your needs

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: =?iso-8859-1?q?Zabair=20Ahmed?=
INET: roon987_at_yahoo.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Feb 11 2003 - 10:19:17 CST