From daemon Sun Dec 31 14:03:34 1995 Received: from ccvm.sunysb.edu by alice.jcc.com; (5.65v3.0/1.1.8.2/01Aug94-0142PM) id AA26757; Sun, 31 Dec 1995 14:03:32 -0500 Message-Id: <9512311903.AA26757@alice.jcc.com> Received: from CCVM.SUNYSB.EDU by ccvm.sunysb.edu (IBM VM SMTP V2R3) with BSMTP id 0626; Sun, 31 Dec 95 13:50:22 EST Received: from CCVM.SUNYSB.EDU (NJE origin LISTSERV@SBCCVM) by CCVM.SUNYSB.EDU (LMail V1.2a/1.8a) with BSMTP id 3377; Sun, 31 Dec 1995 13:50:20 -0500 Date: Sun, 31 Dec 1995 10:49:41 -0800 Reply-To: "ORACLE database mailing list." Sender: "ORACLE database mailing list." From: "Umesh K. Tiwari" Subject: Re: Need Help Rebuilding an Index X-To: ORACLE-L@CCVM.SUNYSB.EDU To: Multiple recipients of list ORACLE-L You wrote: Looks like you seem to be using default extent allocation parameters assigned during the tablespace creation for all of the tables and indexes. This may not be the best way to create tables and indexes, especially when you already know the data volume of each of the objects. Try to specify the initial extent size close to the maximum requirement of each of the objects you create (the size is limited by the amount of contiguous space you can find on the tablespace to allocate). The next extent size can be adjusted according to the availability of contiguous chunks on your tablespace afterwards. I am afraid there's no fixed formula to arrive at a number, but we can certainly make an intelligent guess in advance, to avoid the king of problem you mentioned. However, your situation doesn't seem something to be alarmed. Just pick up the table or index on which extent allocation has failed, and adjust its next extent size by 'alter object storage (whatever..)' to make it use the then available contiguous space on the tablespace. Feel free to write if you have further questions. Goo luck, and happy new year! Umesh K. Tiwari > >Hi. > >I need to rebuild an index on a big table and don't have enough adjacent >free space in that tablespace to do it. > >This tablespace (LGIDX) contains only large (>50MB) indexes. Right now, it >contains only 5 objects. These objects are also indexes. >The tablespace would be big enough to hold these indexes *plus* the one I'm >attempting to rebuild *if* it was not fragmented. > >Here's what I would like to do: > >1. Make a cold backup >2. Create a script to re-build the indexes that already exist in > that tablespace. >3. Drop the tablespace using "alter tablespace LGIDX offline drop" >4. Re-create the tablespace using (same size, on same disk drive): > > SQLDBA> create tablespace LGIDX > datafile '/u15/oradata/plgidx001.dbf' size 250M, > '/u15/oradata/plgidx002.dbf' size 250M > default storage ( > initial 10M next 10M > minextents 1 maxextents 99 > pctincrease 0) > online; >5. Re-create the indexes in the newly de-fragmented tablespace using the > script I created in #2. >6. Make another cold backup. > >Will this work? Or should I just add another datafile to the LGIDX >tablespace and skip all this extra work? > >Thanks! > >Joe Johnson >Highland Community College >