| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index rebuilds and TEMP segments..
Try rebuild of index with lower initial and next extent size based on
dba_free space and coalesce your target tablespace before rebuild.
Regards
Rafiq
From: "TheOracle DBA" <theoracledba_at_lycos.com>
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Subject: Re: Index rebuilds and TEMP segments..
Date: Thu, 09 Nov 2000 13:22:05 -0800
I am assuming your REBUILD is putting the newly rebuilt index in the same TS as the source was.
I have <heard> that REBUILD actually:
1) creates another segment, 2) issues the CREATE INDEX (sorts to TEMP), 3) kills the old index, 4) then renames the new segment to the old index name,and the world continues to spin.
Therefore, you need X amount of free space in the "using for both of my indexes" TS (where X = bytes of source index) for the REBUILD plus space in TEMP for the sort.
Cheers,
Earl
On Thu, 09 Nov 2000 12:27:36
Shawn Ferris wrote:
>What am I missing:
>
>During a migration cycle, transforming legacy db's into the new app, the
>developers are rebuilding their indexes at strategic locations. (They
don't
>actually check whether it's necessary, they just do.) During the index
>rebuild, I believe they issue 'alter index ... rebuild ...', we get the
>following error:
>
>ORA-1652: unable to extend temp segment by 3200 in tablespace
DI_I
>
>DI_I is the tablespace that the indexes belong in. We've watched the temp
>segments come and go, in this tbsp. We watch the space as the rebuild
>happens and at minimum there is 1.5gb free. I'm assuming the storage for
>these segments are taken from the tbsp default and there is no way to
alter
>that behavior. The largest extent size is greater than the tbsp's default
>initial and next. (There is plenty of room for growth from what I can
tell.)
>
>(I'm completely guessing here.. Please correct me everywhere that I am
>wrong.) My suspicion is that as they rebuild these indexes, each time they
>allocate new extents. Possibly these extents are marked for data use only
>and not useable for temp segments later. Each time they rebuild, they use
up
>all the space available to allocate for temp segments..
>
>If this is true, how do you alleviate this problem, given that I don't
have
>any control over the code. (third party) My thought was to try and
alternate
>between 2 tbsps, coalescing the last tbsp after all the objects move to
the
>new? (Which I would assume would mark them as useable space for any type
>segment.)
>
>Any help will be greatly appreciated!
>
>Shawn M Ferris
>Oracle DBA - Time Warner Telecom
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Shawn Ferris
> INET: Shawn.Ferris_at_twtelecom.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).
>
Get FREE Email/Voicemail with 15MB at Lycos Communications at http://comm.lycos.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: TheOracle DBA INET: theoracledba_at_lycos.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). _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.Received on Thu Nov 09 2000 - 16:44:39 CST
 |
 |