-
Rebuilding indexes.....
Hi Gurus,
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
IDX_PAYSTATPERF 223MB
PK_DS_PAY_STATUSES 111MB
IDX_OATCUSTIDACCTNO 456MB
IDX_OABCUSTIDACCTNO 350MB
SYS_C0061007 833MB
------
TOTAL 2378MB
------
ds_index_ts - IDX_DS_PAY_TXNS 143MB
IDX_PERF_DS_OA_TXNS 503MB
SYS_C0058752 668MB
DS_OA_BALANCES_IDX4 207MB
IDX_CUSTACCT 225MB
SYS_C0058758 1512MB
XIF18DS_OA_BALANCES 750MB
XIF19DS_OA_BALANCES 309MB
XIF17DS_PAY_INVOICE_DTLS 169MB
XIF22DS_PAY_EXCEPTIONS 2MB
------
4488MB
------
ds_txn_ts - SYS_C0058757 77MB
XIF16DS_PAY_STATUSES 80MB
SYS_C0058753 191MB
SYS_C0030685 0.3 (311296 bytes)
SYS_C0030690 0.73 (761856 bytes)
SYS_C0030681 1.5MB
SYS_C0031608 494MB
------
844.53MB
------
ds_par_ts - XIF19DS_PAY_FXCONTRACT_DTLS 0.58 (606208 bytes)
XIF18DS_PAY_DOC_CHECKLISTS 1.31MB
------
1.89MB
------
Rgds,
Saravanan.
-
First question you have to ask yourself is why are you doing it?
Secondly that first tablespace will blow when you try and rebuild
SYS_C0061007
Thirdly, why do you have an index of 0.5M on that last, a FTS maybe faster than that index
-
Rebuild index...
Hi Dave,
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,
Saravanan.
-
Hi shravansam
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.
Good luck
-
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
from dba_segments
where tablespace_name='SYSTEM'
and owner not in ('SYS','SYSTEM')
And this one for indexes.
SELECT OWNER,INDEX_NAME
FROM DBA_INDEXES
WHERE TABLESPACE_NAME='SYSTEM'
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...
Spool d:\Pxxx_index_rebuild.log
set echo on;
Blah blah blah
spool off;
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.
MH
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|