This content has been marked as final. Show 6 replies
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
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
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).
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>
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