9 Replies Latest reply: Oct 5, 2012 1:03 PM by jgarry RSS

    Table/Index actual reclaimable free space

    Vikash Jain (DBA )
      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
          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 )
            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....
              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 )
                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....
                  What exactly you didn't get?

                  Aman....
                  • 6. Re: Table/Index actual reclaimable free space
                    sb92075
                    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
                      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 )
                        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
                          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)