NEED YOUR ADVISE ON THIS

[Ablakios]

Hello to all the Gurus
We are in the process of database reorg. We are running on Oracle 8.1.7 on Windows 2000
advanced server.

We currently have dictionary managed tablespaces and our USERS_DATA and INDEX tablespaces are heavily loaded.

USERS_DATA TABLESPACE is 16gb with about 1200 tables(Large and small) with 6 datafiles.
INDEX TABLESPACE is 21 gb with 8 datafiles.

We pre created 4 different Locally managed tablespaces just for the USERS_DATA
USERS_DATA1
USERS_DATA2
USERS_DATA3
USERS_DATA3
just to move the large and frequently used tables.
and doing the same for the INDEX tables.
We know that after Moving those tables we will REBUILD index for those tables moved as they will have different row id.

Our question is really about MOVING the INDEX
How do we handle that?

Do we just rebuild index of the tables moved and just move those particular indexes in the INDEX tablespace?

What's the best approach?

[Sameer]

Hi,

I would suggest to rebuild only those indexes which get invalid.

And may be after relocating everything ANALYZE the complete database overnight.

Sameer

[marist89]

When you move a table, its underlying indexes will become invalid. You can rebuild them using:
ALTER INDX xyz_pk REBUILD TABLESPACE ind_ts1