This content has been marked as final. Show 9 replies
You can analyze the table and later run segment advisor or use dbms_space.free_space
Thanks Fran for the suggestion,
But in this case how will i come to know that this table might have this much free space ? i want to know which table has free space and how much space that table have , So according to that i will shrink the space for those particular tables/Indexes alone ...
Thanks & Regards
If you are on db version 10g and above, the same can be shown to you from it quite nicely. Otherwise, you may want to use the package dbms_space for the same.
Hi Aman ,
I am using oracle 11g R2 64 bit version.
i am not able to get u on this ?
the same can be shown to you from it quite nicely How can i get ?
What exactly you didn't get?
Vikash Jain (DBA Trainee) wrote:"reclaim" space from where to where?
I have a doubt in table /index actual size , I know how to shrink the object but i do not know how to check that this table is having this much free blocks and i can get this much of free space from that object ..
When i ran this below script for getting table info like this .
select blocks, empty_blocks,avg_space, num_freelist_blocks,ROW_MOVEMENT,LAST_ANALYZED from user_tables where table_name = 'USER_MAS'
all columns are blank except blocks (49883)...
After that i just analyzed the table using below script
Analyze table user_mas compute statistics;
and again i ran the same script to check table info and i got some empty blocks(86) .
so here my doubt is how to get the actual size of the object (Used and Free) and how to calculate the reclaimable space from that object so that i can be sure that if i shrink the object then i will get this much of free space from that object ..
Thanks & Regards,
Why do you feel that such activity is worthwhile & long lasting?
How to reclaim UNUSED_SPACE from Indexes and Tables using DBMS_SPACE.UNUSED_SPACE
Edited by: Girish Sharma on Oct 4, 2012 9:05 PM
One more :
I want to know how much actually free space is there in table/index ... According to that i will take decision to shrink the object for reclaiming the free space . in my environment , we are giving only limited size for table space for exp 2 GB ... but suppose that 2 GB fully occupied then i will check the object which occupied this space . So if i want to shrink the tables then how will i come to know that this table is having this much free space so after shrinking the table i will get that free space .
I hope you got my point ...
Thanks & Regards,
The dbconsole segment advisor display Recommendation Details for Tablespace shows how much you can expect to get back for each segment. I've found it a bit optimistic for my usage patterns, YMMV. "The recommendations are based on the growth trend of the segment. If the growth trend for the segment is available, the reported space usage will be projected data and may not be equal to the space usage at the time of evaluation. " (from 10gR2 help)