6 Replies Latest reply on Apr 25, 2012 12:12 PM by 917041

    Tablespace Discrepency


      I have a tablespace of size 512 GB. On the basis of tables and indexes created on it, the space consumed should be 319GB but when I am retrieving the free space size , I am getting only 124GB of free space. That means around 70GB of space is missing. Can any one help me out to find that missing space

        • 1. Re: Tablespace Discrepency
          What's the db version and o/s ? How are you measuring the space usage within the tablespace?

          • 2. Re: Tablespace Discrepency
            I am using 11g with OS as IBM AIX.
            I am using DBA_SEGMENTS to check the size of the objects of that tablespace.
            • 3. Re: Tablespace Discrepency
              Can you post the query that you are using?

              • 4. Re: Tablespace Discrepency
                To get the size segment wise:

                select SEGMENT_NAME,(sum(BYTES)/(1024*1024*1024)) "SIZE" from dba_segments where TABLESPACE_NAME like 'MOTOR_QUOTE' group by SEGMENT_NAME

                To get free space

                select TABLESPACE_NAME,sum(BYTES)/1024/1024/1024 from dba_free_space where TABLESPACE_NAME like 'MOTOR_QUOTE' group by TABLESPACE_NAME
                • 5. Re: Tablespace Discrepency
                  That means around 70GB of space is missing. Can any one help me out to find that missing space
                  There isn't any space 'missing'. You are comparing apples and oranges.

                  DBA_SEGMENTS shows space (extents) actually used - not space (extents) allocated but not yet used.

                  DBA_FREE_SPACE shows extents that haven't been allocated.

                  The 70GB you aren't seeing is for extents that have been allocated but have not yet been used.

                  See Girish Sharma's replyl Posted: Mar 6, 2012 8:57 PM in this thread for more details
                  Re: HWM Question

                  For a single test table HWM (high-water-mark) here are the numbers
                  SQL> select blocks, extents from dba_segments where segment_name='HWM';
                      BLOCKS    EXTENTS
                  ---------- ----------
                          96         12
                  SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='HWM';
                      BLOCKS EMPTY_BLOCKS   NUM_ROWS
                  ---------- ------------ ----------
                          88            8       5000
                  SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from HWM;
                  used blocks
                  Note that DBA_SEGMENTS shows 96 blocks allocated but DBA_TABLES shows that 88 of those blocks have been formatted for data and that 8 of those are empty (allocated but not formatted).

                  The last query shows that only 82 of the blocks actually have data in them.

                  There is a 'show_space' procedure on the AskTom site
                  • 6. Re: Tablespace Discrepency
                    Thanks a lot for your help.