Reclaiming Unused Space in Index.

We can see Reclaimable space in Schema by using below query.

SELECT'Task Name : ' || f.task_name || CHR(10) ||'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||'Segment Name : ' || o.attr2 || CHR(10) ||'Segment Type : ' || o.type || CHR(10) ||'Partition Name : ' || o.attr3 || CHR(10) ||'Message : ' || f.message || CHR(10) ||'More Info : ' || f.more_info || CHR(10) ||
'------------------------------------------------------' Advice FROM dba_advisor_findings f,dba_advisor_objects o,dba_advisor_executions e WHERE o.task_id = f.task_id AND o.object_id = f.object_id AND f.task_id = e.task_id AND e. execution_start > sysdate - 1 AND e.advisor_name = 'Segment Advisor' ORDER BY f.task_name;

Solution

There are a couple of effective methods for freeing up unused space associated with an index:




•Rebuilding the index
•Shrinking the index


Before you perform either of these operations, first check
USER_SEGMENTS to verify that the amount of space used corresponds with the Segment Advisor’s advice. In this example, the segment name is F_REGS_IDX1

SQL> select bytes from user_segments where segment_name = 'F_REGS_IDX1';
BYTES----------
166723584

This example uses the
ALTER INDEX...REBUILD

statement to re-organize and compact the space usedby an index:

SQL> alter index f_regs_idx1 rebuild;
Alternatively, use the
ALTER INDEX...SHRINK SPACE statement to free up unused space in an index—for example:

SQL> alter index f_regs_idx1 shrink space;

Now query

USER_SEGMENTS

again to verify that the space has been de-allocated. Here is the output forthis example:

BYTES----------
524288

The space consumed by the index has considerably decreased.

I prefer export and import is the another useful method to reclaim free space.

People who read this post also read :



19 comments:

Players operating in the market also face challenges which are impeding their development and growth.
sap erp system

" Thank you for your posting.
Seems like a nice blog.
Stock Tips

you discuss this topic,its very useful for the business people all are want to new update so thanks for sharing

Comfortabl y, the post is actually the sweetest on this deserving topic. I concur with your conclusions and will eagerly look forward to your upcoming updates. Saying thanks will not just be adequate, for the phenomenal lucidity in your writing. I will right away grab your rss feed to stay informed of any updates. Pleasant work and much success in your business endeavors!

Thanks a lot for enjoying this beauty article with me. I am apreciating it very much! Looking forward to another great article. Good luck to the author!

i like your post.really loveaval post and all information are very usefull .

Excellent post. This article is really very interesting and effective. I think its must be helpful for us. Thanks for sharing your informative.

Welcome to the Indian and pakistani Model Agency in malaysia. We are advancing Indian and Pakistani Escort agency in Malaysia. Our Escorts don't do simply Escort advantages yet they are capable models. As we are Malaysia's most assumed Escort office so we moreover passage thee well while selecting stunning model Escorts in Malaysia. Our Escorts are normal great woman from outside and moreover inside so they are the perfect choice for an extensive variety of Malaysia Escort services.Please contact me on +60182924827.And visit our site
http://www.malaysiashowbiz.com/
http://malaysiashowbiz.tumblr.com/

Welcome to the Indian and pakistani Model Agency in malaysia. We are advancing Indian and Pakistani Escort agency in Malaysia. Our Escorts don't do simply Escort advantages yet they are capable models. As we are Malaysia's most assumed Escort office so we moreover passage thee well while selecting stunning model Escorts in Malaysia. Our Escorts are normal great woman from outside and moreover inside so they are the perfect choice for an extensive variety of Malaysia Escort services.Please contact me on +60182924827.And visit our site
http://www.malaysiashowbiz.com/
http://malaysiashowbiz.tumblr.com/

Wow, Excellent post. This article is really very interesting and effective. I think its must be helpful for us. Thanks for sharing your informative.

hd movies
full hd movie free download | free movie download | hd movie download | full hd movie free download | full hd movie free download | hd movie download | full hd movie free download | full hd movie free download

Truly a very good article on how to handle the future technology. After reading your post,thanks for taking the time to discuss this, I feel happy about and I love learning more about this topic
Manpower Consultancy in Bangalore
Hr Consultancy in bangalore
Recruitment Consultancy in Bangalore

Most of the development firm introduce theERP Software India
Please visit.

very very amazing explaintion....many things gather about yourself...yes realy i enjoy it
SEO Company in India

wow really superb you had posted one nice information through this. Definitely it will be useful for many people. So please keep update like this.

Logistics Software
Fleet Management Software
ERP Software Companies

thanks for the explain to detail,,,, its too use ones again thank u so much sir......


ccna training in chennai



ccna training in bangalore


ccna training in pune

myTectra Placement Portal is a Web based portal brings Potentials Employers and myTectra Candidates on a common platform for placement assistance

Post a Comment

Share

Twitter Delicious Facebook Digg Stumbleupon Favorites More