Re: Need Help Rebuilding an Index

From: TSAWMILL.US.ORACLE.COM <TSAWMILL_at_US.ORACLE.COM>
Date: Fri, 29 Dec 1995 12:06:37 EST
Message-Id: <9512301925.AA03310@alice.jcc.com>

--Boundary-7578184-0-0

You know, upon further consideration, you don't really need the cold backups since all you are re-creating are indexes. No tables in that tspace, right?

Tim Sawmiller
tsawmiller_at_us.oracle.com
"I speak for me and no else!"

--Boundary-7578184-0-0

Content-Type: message/rfc822

Date: 29 Dec 95 08:18:17
From:"Joe Johnson " <HCC_JOHNSON_at_DELPHI.COM> To: Multiple,recipients,of,list,ORACLE-L,ORACLE-L_at_CCVM.SUNYSB.EDU Subject: Need Help Rebuilding an Index Reply-to: ORACLE-L_at_CCVM.SUNYSB.EDU
X-Orcl-Application: Sender: "ORACLE database mailing list."  <ORACLE-L_at_CCVM.SUNYSB.EDU>
X-To: oracle-l_at_ccvm.sunysb.edu

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

--Boundary-7578184-0-0--
Received on Sat Dec 30 1995 - 14:25:16 CST