INDX tablespace in increasing
In our production database, we've two index tablespaces. These are continuously increasing (about 300 mb per day). Some days before i've added some more datafiles to these tabelspace. But it is not possible to add space everytime. So what should be the reason behind it?How can I analyze this? How do I proceed to solve this issue?
Thanks in advance.
Enable index usage monitoring and drop the unused indexes to recover the space.
I dont think you can do anything else as index consumes data as your loading the data into base tables. Either not to load the data or drop the indexes to avoid space usage
have you cheked which indexes are growing most?
have you cheked how the tables realted to these indexes are growing?
are these table growing as exepcted?
did you make any estimation how the tables and indexes are going to grow over the time?
You'll just have to stop those pesky users from using the database...
Originally Posted by minal_yawale
Assistance is Futile...
Take tree dump of the index that you suspected growing fast and post the same here.
the first thing shoud be checking if the table is growing. taking dumps is rather severe for a beginner like him
Thanks for ur suggetions.
I've started analyzing the indexes.
Then fired this query:
SELECT name,height,lf_rows,del_lf_rows, USED_SPACE
It shows following output (for one indx)
NAME HEIGHT LF_ROWS DEL_LF_ROWS USED_SPACE
PK_ORDER_ID 1 22 0 570 and total space of that indx is 10 MB and used space =570, pct_used=8,LF_BLK_LEN=8000,BTREE_SPACE=8000
What does it mean ? Used space is in bytes right ?
Also I've tried this query
alter index PK_ORDER_ID monitoring usage
select index_name,used from v$object_usage where index_name='PK_ORDER_ID'
But it shows nothing.
I am not able to understand what to do from this result. Can u pls guide me about this and tell steps how to proceed further ?
Thanks for your valuable time.
Can you answer the following question?
Does the table data grow with respect to the index growth?
Yes, that table data also growing with respect to indexes.
so whats the problem, data id going into your tables and the indexes are growing?
Click Here to Expand Forum to Full Width