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.
Tablespace Indexes size
---------- ------- -----
ds_default_ts - IDX_PAYTXNPERF 405MB
ds_index_ts - IDX_DS_PAY_TXNS 143MB
ds_txn_ts - SYS_C0058757 77MB
SYS_C0030685 0.3 (311296 bytes)
SYS_C0030690 0.73 (761856 bytes)
ds_par_ts - XIF19DS_PAY_FXCONTRACT_DTLS 0.58 (606208 bytes)
First question you have to ask yourself is why are you doing it?
Secondly that first tablespace will blow when you try and rebuild
Thirdly, why do you have an index of 0.5M on that last, a FTS maybe faster than that index
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.
Thanks & Regards,
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.
Well, I do hope I am worng but this is starting to sound like you need some auditing ASAP.
First of all, do you have ANYTHING in the SYSTEM tablespace that is not owned by SYS or SYSTEM?
Try this for starters.
select owner, segment_name, segment_type
and owner not in ('SYS','SYSTEM')
And this one for indexes.
AND OWNER NOT IN ('SYS','SYSTEM')
The only other user that should have objects in there is OUTLN.
If you have tables and indexes in the system tablespace you should re-build them to another tablespace ASAP.
Will you create a new tablespace for the re-build?
Will they be online?
Personally I like to spool a script for the rebuild and include these parameters in that re-build script...
set echo on;
Blah blah blah
This way you have a log of all the re-builds, if there are any errors you can always refer to the log and make adjustments.
I remember when this place was cool.
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.
Click Here to Expand Forum to Full Width