This discussion is archived
6 Replies Latest reply: Apr 25, 2012 5:12 AM by 917041 RSS

Tablespace Discrepency

917041 Newbie
Currently Being Moderated
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.... Oracle ACE
    Currently Being Moderated
    What's the db version and o/s ? How are you measuring the space usage within the tablespace?

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

    Aman....
  • 4. Re: Tablespace Discrepency
    917041 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    Thanks a lot for your help.

Legend

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