RE: TEMP Segments

From: Benhayoune khalid <benhayoune_at_maroclear.co.ma>
Date: Mon, 15 May 2000 18:14:07 -0000
Message-Id: <10498.105682@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

=_NextPart_001_01BFBE99.5C942E10 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable

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 :

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-----
De : Wayne S Bellefeuille [mailto:wbellefe_at_allina.com] Envoy=E9 : lun. 15 mai 2000 18:26
=C0 : Multiple recipients of list ORACLE-L
Objet : TEMP Segments

Running Oracle 8.0.5 on AIX...

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

In retrying, the rebuild, I get this message: 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
hanging out in tablespace PMADTLX. I thought SMON should clean up = these
TEMPORARY segments automatically.

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

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

Wayne

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

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

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).

=_NextPart_001_01BFBE99.5C942E10 Content-Type: text/html; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =

5.0.1459.75">

<TITLE>RE: TEMP Segments</TITLE>
</HEAD>
<BODY>

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?

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

SQL> column c noprint new_value v_pct; SQL> select pct_increase c from dba_tablespaces = where tablespace_name =3D 'TEMP'; SQL> alter tablespace temp default storage = (pctincrease &&v_pct); 
 SQL> exit

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.

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

-----Message d'origine----- De : Wayne S Bellefeuille [<A = HREF=3D"mailto:wbellefe_at_allina.com" =
TARGET=3D"_blank">mailto:wbellefe_at_allina.com</A>]

<BR><FONT SIZE=3D2>Envoy=E9 : lun. 15 mai 2000 18:26</FONT>
<BR><FONT SIZE=3D2>=C0 : Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Objet : TEMP Segments</FONT>
</P>
<BR>

Running Oracle 8.0.5 on AIX...

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

In retrying, the rebuild, I get this message: 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
 hanging out in tablespace PMADTLX.  I thought = SMON should clean up these
 TEMPORARY segments automatically.

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

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

Wayne

<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Wayne S Bellefeuill</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: wbellefe_at_allina.com</FONT>
</P>

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

SIZE=3D2>---------------------------------------------------------------=

-----
 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 = Received on Mon May 15 2000 - 13:14:07 CDT