This discussion is archived
9 Replies Latest reply: Oct 5, 2012 11:03 AM by jgarry RSS

Table/Index actual reclaimable free space

Vikash Jain (DBA ) Newbie
Currently Being Moderated
Hi all,

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,
Vikash jain
  • 1. Re: Table/Index actual reclaimable free space
    Fran Guru
    Currently Being Moderated
    You can analyze the table and later run segment advisor or use dbms_space.free_space
  • 2. Re: Table/Index actual reclaimable free space
    Vikash Jain (DBA ) Newbie
    Currently Being Moderated
    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
    Vikash Jain
  • 3. Re: Table/Index actual reclaimable free space
    Aman.... Oracle ACE
    Currently Being Moderated
    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.

    Aman....
  • 4. Re: Table/Index actual reclaimable free space
    Vikash Jain (DBA ) Newbie
    Currently Being Moderated
    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 ?
  • 5. Re: Table/Index actual reclaimable free space
    Aman.... Oracle ACE
    Currently Being Moderated
    What exactly you didn't get?

    Aman....
  • 6. Re: Table/Index actual reclaimable free space
    sb92075 Guru
    Currently Being Moderated
    Vikash Jain (DBA Trainee) wrote:
    Hi all,

    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,
    Vikash jain
    "reclaim" space from where to where?

    Why do you feel that such activity is worthwhile & long lasting?
  • 7. Re: Table/Index actual reclaimable free space
    Girish Sharma Guru
    Currently Being Moderated
    How to reclaim UNUSED_SPACE from Indexes and Tables using DBMS_SPACE.UNUSED_SPACE

    http://www.akadia.com/services/scripts/how_to_reclaim_unused_space.txt

    Regards
    Girish Sharma

    Edited by: Girish Sharma on Oct 4, 2012 9:05 PM
    One more :

    http://www.oracle-base.com/dba/script.php?category=monitoring&file=unused_space.sql
  • 8. Re: Table/Index actual reclaimable free space
    Vikash Jain (DBA ) Newbie
    Currently Being Moderated
    Sorry Aman,

    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,
    Vikash jain
  • 9. Re: Table/Index actual reclaimable free space
    jgarry Guru
    Currently Being Moderated
    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)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points