The Oracle (tm) Users' Co-Operative FAQ

How do I find out how much space is actually USED by a table/index


Author's name: Jonathan Lewis

Author's Email: Jonathan@jlcomp.demon.co.uk

Date written: 13th Nov 2000

Oracle version(s): 7.3 - 8..0.5

I can see from the views dba_extents, dba_segments that a table called base_table has 500 blocks allocated to it, but if I look in the view dba_tables, the blocks column says that base_table has 253 blocks allocated - which of these is correct ?

Back to index of questions


The views dba_extents and dba_segments tell you how much space has been reserved in the database for an object. The blocks column tells you the highest point in that reserved space that your data had reached the last time you analyzed the table. In other words, one figure tells you how much space you have reserved, the other tells you how much you have used.

Of course, there are complications - if your application tends to insert 100 rows, then delete 35, and keeps repeating the cycle, you may find (using slightly more subtle techniques) that there are lots of small holes in the table which are below the 'high water mark'. You may have been using space in wasteful fashion when you could have been reclaiming it and re-using it, so your 'high water mark' isn't quite as helpful as you might hope. Nevertheless, if you have used the ANALYZE command fairly recently, you can assume that if you rebuild the table, then the number of blocks needed would be no more than the value given by the blocks column of the dba_tables view.

The same sort of argument applies to indexes, although the column available in the dba_indexes is called leaf_blocks and, as its name suggests, does not count the root block or branch blocks - as a rough guideline, this value will typically be about 1% or 2% short as a result.

If you do not want to ANALYZE your tables and indexes (which may be the case if part of your database is running a 3rd party application that depends implicitly on running rule-based optimisation), there is an alternative way of finding the high water mark of tables and indexes. Use the unused_space procedure in the dbms_space package, owned by the SYS account.

The procedure takes eleven parameters, four of them IN parameters, seven of them OUT parameters, so it requires a little preparation, and has to be called from a PL/SQL block. (Earlier versions of this package had only three IN parameters, the last one is the partition_name which had to be introduced with Oracle 8 to allow for finding the highwater mark for each segments of a partitioned object).

There is a PUBLIC SYNONYM for dbms_space, and the execute privilege has been granted to PUBLIC, so anyone can call this package. There is a simple example of its use below, and a more sophisticated example reference in the section on further reading.


Sample of using dbms_space

set serveroutput on size 1000000 format wrapped

declare
	m_total_blocks		number;
	m_total_bytes		number;
	m_unused_blocks		number;
	m_unused_bytes		number;
	m_last_ext_file_id	number;
	m_last_ext_block_id	number;
	m_last_used_block	number;

begin

	dbms_space.unused_space(
		segment_owner	=> 'JPL1',
		segment_name	=> 'BASE_TABLE',
		segment_type	=> 'table',
		total_blocks	=> m_total_blocks,
		total_bytes	=> m_total_bytes,
		unused_blocks	=> m_unused_blocks,
		unused_bytes	=> m_unused_bytes,
		last_used_extent_file_id	=> m_last_ext_file_id,
		last_used_extent_block_id	=> m_last_ext_block_id,
		last_used_block			=> m_last_used_block,
		partition_name	=> null
	);

	dbms_output.put_Line(
		'Allocated blocks: ' || m_total_blocks || ' ' || 
		'Used blocks: ' || (m_total_blocks - m_unused_blocks)
	);

end;
/

Allocated blocks: 512 Used blocks: 2 

PL/SQL procedure successfully completed.

;

Notes:

The segment_name and segment_owner are case sensitive, but the segment_type is not.

The last_used_block identifies the last used block in the extent holding the high water mark, it is not an absolute block address, so it may be a lot smaller than you expect. e.g. if last_used_block = 1 it means you have only used one block from the last extent.

Although the segment types listed below are technically valid, some of them are not accepted by all the versions of Oracle, and using some of them will result in a session crashing:

	INDEX
	INDEX PARTITION
	INDEX SUBPARTITION
	LOBINDEX
	LOBSEGMENT
	TABLE
	TABLE PARTITION
	TABLE SUBPARTITION

Further reading: An example of using the dbms_space package


Back to top

Back to index of questions