I am going to Rebuild some of the indexes in our database. I have given the space available in the system,temp and the tablespaces where the indexes are there. Pls guide me whether the tablespaces having enough space to rebuild or not? What r the steps i need to do before rebuild? Thanks in advance.
system tablespace having 155MB freespace.
Temp tablespace having 2475MB freespace.
ds_default_ts has 7 datafiles , which is having freespace of totally 769MB.
ds_index_ts has 5 datafiles, which is having freespace of totally 7477MB.
ds_txn_ts has 7 datafiles, which is having freepspace of totally 1473MB.
ds_par_ts has 4 datafiles, which is having freespace of totally 248MB.
I have given the existing size of the indexes below.
Y we r rebuilding is, we r currently doing purging in that tables (7 million records). Bcs of avoiding fragmentation, and performance we plan to rebuild.
If i run other indexes one by one there won't be a problem right ? How can i run SYS_C0061007 ? Do i need to create one more tablespace and move this SYS_C0061007 to new tablespace when rebuild or anyother way ? Pls suggest.
I think that you will not have problem with the rebuild, maybe for the SYS_C0061007 index in ds_default_ts tablespace, you can rebuild it in other tablespace, which has more free space than it curent size, and after that to rebuild it again in the first tablespace.
Also may advice is to consider using COMPRESS on the indexes, it can save space and leads to better performance (that wont work well on PK/unique indexes with one column or if the first column in the index is unique). And you can alter the session in which you will rebuild the indexes with bigger SORT_AREA_SIZE.
Also, why are you separating tables and indexes into different TS's?
COMPRESS may be counter productive on an OLTP system, as it can increase block contention for index modifications. It's not always a bad thing that an index is spread out. Just something you should be aware of.