6 Replies Latest reply: Apr 25, 2012 7:12 AM by 917041 RSS

    Tablespace Discrepency

    917041
      Hi...

      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

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

          Aman....
          • 2. Re: Tablespace Discrepency
            917041
            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
              Aman....
              Can you post the query that you are using?

              Aman....
              • 4. Re: Tablespace Discrepency
                917041
                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
                  rp0428
                  >
                  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
                  -----------
                           82
                   
                  SQL>
                  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
                  http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5350053031470
                  • 6. Re: Tablespace Discrepency
                    917041
                    Thanks a lot for your help.