RE: TEMP Segments

From: Norwood Bradly A <Bradley.A.Norwood_at_M1.IRSCOUNSEL.TREAS.GOV>
Date: Mon, 15 May 2000 15:39:39 -0400
Message-Id: <10498.105686@fatcity.com>

Or you can issue the command 'oradebug wakeup {pid}. pid is the process id of smon.

-----Original Message-----
From: Benhayoune khalid [mailto:benhayoune_at_maroclear.co.ma] Sent: Monday, May 15, 2000 3:09 PM
To: Multiple recipients of list ORACLE-L Subject: RE: TEMP Segments

One of the tasks of SMON is to cleanup temporary segments of aborted transactions. So, after the next wakeup of SMON this segment will be dropped. But the next wakeup of SMON can take more than two hours from = now
and you have to take action immediately to prevent other sessions to = fail as
well and you cannot bounce the Oracle instance. So, how do you get rid = of
this temporary segment as quickly as possible?=20

 =20
connect to SQL*Plus as SYSTEM (or SYS) and execute the following = commands :=20

SQL> column c noprint new_value v_pct;=20 SQL> select pct_increase c from dba_tablespaces where tablespace_name =
=3D

'TEMP';=20
SQL> alter tablespace temp default storage (pctincrease &&v_pct);=20 SQL> exit=20

First, the current pct_increase value of the TEMP tablespace is = selected and
stored in the v_pct variable. Then this v_pct variable is used to ALTER = the
tablespace with the same value for pct_increase. This seems strange, = but is
done to force a data dictionary change which immediately triggers SMON. = SMON
wakes up, detects a temporary segment of an aborted transaction and immediately drops it.=20

This is the fastest method (so far I know) to cleanup temporary = segments
without bouncing the Oracle instance.=20

-----Message d'origine-----=20
De : Wayne S Bellefeuille [ mailto:wbellefe_at_allina.com <mailto:wbellefe_at_allina.com> ]=20
Envoy=E9 : lun. 15 mai 2000 18:26=20
=C0 : Multiple recipients of list ORACLE-L=20
Objet : TEMP Segments=20

Running Oracle 8.0.5 on AIX...=20

In doing rebuilds of indexes, I am blowing up with the following error: =

ORA-01652: unable to extend temp segment by 2048 in tablespace PMADTLX=20

In retrying, the rebuild, I get this message:=20 ORA-01658: unable to create INITIAL extent for segment in tablespace = PMADTLX In looking at DBA_SEGMENTS, there are a whole bunch of TEMPORARY = segment
types=20
hanging out in tablespace PMADTLX. I thought SMON should clean up = these=20
TEMPORARY segments automatically.=20

I am assuming this is a bug in Oracle, but thought I would ask around.=20

Anyone know a full-proof "trick" to get rid of these. I tried creating = a
dummy=20
table with a small INITIAL and big NEXT with MINEXTENTS 2. It blew up = on
the=20
NEXT extent, but did not clean up the tablespace.=20

Wayne=20

--=20
Author: Wayne S Bellefeuill=20
  INET: wbellefe_at_allina.com=20

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051=20
San Diego, California        -- Public Internet access / Mailing Lists=20
--------------------------------------------------------------------=20

To REMOVE yourself from this mailing list, send an E-Mail message=20 to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in=20 the message BODY, include a line containing: UNSUB ORACLE-L=20 (or the name of mailing list you want to be removed from). You may=20 Received on Mon May 15 2000 - 14:39:39 CDT