JL Computer Consultancy

Free Space without the Analyze.

November 2000 / March 1998

Addendum 31st Jan 2001: Oracle has moved on a lot since this page was first written. The number of blocks on free lists is now stored in the freelist group blocks, or on the segment header if you have just one freelist group. It is possible, therefore, that the procedure no longer walks the freelist(s) as it originally did, and the limit put in to reduce the cost of this walk may be redundant. I have not yet checked this hypothesis.

If you need to find out how much free space is left in a table or index, it is possible ANALYZE the object to get relevantg information into dba_tables, dba_indexes etc. However, there are times when you need to get an up to date idea of how much free space there is, but don't want to run the analyze command. For instance the object in question may be large and need a high level of estimate which takes a long time and should only be run at week-ends, or more simply it is a dictionary table which you are not supposed to analyze.

To help you get around this problem, Oracle introduced a package quite a long time ago called dbms_space. The package header can be found in the script $ORACLE_HOME/rdbms/admin/dbmsutil.sql.The package has functions to allow you to discover what point in a segment the high water mark has reached, and also how many blocks are on the free list(s).

The following script is an example of how to use this package - as it stands it has to be run by someone with the DBA privilege. I wrote it as a quick check for a site that had to do a very large amount of object creation and deletion on the production system every day - given the terrible default setup values for the dictionary tables we needed an idea of how much space there was actually available in the data dictionary before we had to add a (raw) file to it.

With a little modification you could turn it into a package that could (for example) be called by an end-user to list only the objects in their own schema.

You will note that this package only addresses tables, indexes, and clusters. There are new segment types in Oracle 8 which the dbms_space does not yet handle. To find the high water mark in these new segment types you might want to look at my block dump note.

Back to Main Index of Topics

rem     Script:        see_space.sql
rem     Author:        J.P.Lewis
rem     Dated:         12-Mar-1998
rem     Purpose:       Report blocks above HWM, and space on the first free list group
rem     Use:
rem     ----
rem     The user of this script is assumed to be a DBA, or at least
rem     to have access to the views DBA_SEGMENTS and v$parameter.
rem     The script produces a very flat report of object name,
rem     partition name if it is partitioned, and the number of
rem     blocks above the High Water Mark.
rem     The walk of the free list is limited to 50 (m_scan_limit) blocks.
column value new_value m_block_size
select value from v$parameter where name = 'db_block_size';
set serveroutput on size 1000000 format wrapped
set linesize 132
set trimspool on
spool see_space
        cursor c1 is
               select owner, segment_name, partition_name, segment_type, 
               blocks, next_extent/&m_block_size next_ext
               from dba_segments
               where segment_type in ('INDEX','TABLE','CLUSTER')
               -- put your choice of restrictions here
               order by owner, segment_type desc, blocks desc, segment_name
        m_tot_blocks   number;
        m_tot_bytes    number;
        m_unused_blocks number;
        m_unused_bytes number;
        m_last_file_id number;
        m_last_block_id number;
        m_last_block   number;
        m_on_free_list number;
        m_free_group   number := 0;
        m_scan_limit   number := 50;
               rpad('Owner',15) || ' ' ||
               rpad('Type',15) || ' ' ||
               rpad('Name',28) || ' ' ||
               rpad('Part Name',28) || ' ' ||
               lpad('Blocks',8) || ' ' ||
               lpad('Next Ext',8) || ' ' ||
               lpad('Unused',6) || ' ' ||
               rpad('-',15,'-') || ' ' ||
               rpad('-',15,'-') || ' ' ||
               rpad('-',28,'-') || ' ' ||
               rpad('-',28,'-') || ' ' ||
               lpad('-',8,'-') || ' ' ||
               lpad('-',8,'-') || ' ' ||
               lpad('-',6,'-') || ' ' ||
        for r1 in c1 loop
                       segment_owner          => r1.owner,
                       segment_name           => r1.segment_name,
                       segment_type           => r1.segment_type,
                       freelist_group_id      => m_free_group,
                       free_blks              => m_on_free_list,
                       scan_limit             => m_scan_limit,
                       partition_name         => r1.partition_name
                       segment_owner                  => r1.owner,
                       segment_name                   => r1.segment_name,
                       segment_type                   => r1.segment_type,
                       total_blocks                   => m_tot_blocks,
                       total_bytes                    => m_tot_bytes,
                       unused_blocks                  => m_unused_blocks,
                       unused_bytes                   => m_unused_bytes,
                       last_used_extent_file_id       => m_last_file_id,
                       last_used_extent_block_id      => m_last_block_id,
                       last_used_block                => m_last_block,
                       partition_name                 => r1.partition_name
                       rpad(substr(r1.owner,1,15),15) || ' ' ||
                       rpad(substr(r1.segment_type,1,15),15) || ' ' ||
                       rpad(substr(r1.segment_name,1,28),28) || ' ' ||
                       rpad(substr(nvl(r1.partition_name,' '),1,28),28) || ' ' ||
                       lpad(to_char(m_tot_blocks,'999,990'),8) || ' ' ||
                       lpad(to_char(r1.next_ext,'99,990'),8) || ' ' ||
                       lpad(to_char(m_unused_blocks,'9,990'),6) || ' ' ||
        end loop;
spool off

Back to Main Index of Topics