Reorganize Index Tablespace

[appi]

Hi All,
we've a huge problem with one of our 8.1.7 DB's. One of our index-Tablespaces is running out of space many times. We added datafiles, but we've lost space in any file. Now I suggest to reorganize this Tablespace, but it's our ODS-Database on which we're not able to design a downtime.
Is there any way to reorganize Tablespaces without generating a downtime in 8.1.7 ?

regards
Uwe

 

[alexhu]

REBUILD the indexes into a new tablespace ?

Alex

 

[AOLEURO]

Hi

ALTER INDEX xxxxx REBUILD [index clause] ONLINE;

1. I would try to use LOCAL management tablespaces with uniform size, because Oracle uses a Bitmap to control free list of extents. And because all extents are the same size, then Oracle can always use a free extent.

2. Then use above ´

ALTER INDEX xxxx REBUILD tablespace XXXXX ONLINE;

to rebuild index into new tablespace.

You will get performance degree during operation, so do not use this during peak-time.


You can just use

ALTER INDEX xxxxx REBUILD;

to rebuild into same tablespace, but if you are using tablespace with diff. extent size, then you will not gain any/much benefit from rebuild. The index is unusable during rebuild.



Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[appi]

Thanks Alex & Allan,
I guess I should discuss this with our development crew. So far I know do we have different extent-sizes.

regards
Uwe

 

[did02]

Hi,

If there is no heavy queries for a while, you can drop indexes, coalesce the index TBS, recreate indexes (with new storage clauses) and analyze them with an online DB: it just decreases performances during these operations.

Rgds,
Did02