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 commad. 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 they are the 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 (at an extra cost) 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.
rem 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 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 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 rem The walk of the free list is limited to 50 (m_scan_limit) blocks. rem
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
declare
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;
begin
dbms_output.put_line(
rpad('Owner',15) || ' ' ||
rpad('Type',15) || ' ' ||
rpad('Name',28) || ' ' ||
rpad('Part Name',28) || ' ' ||
lpad('Blocks',8) || ' ' ||
lpad('Next Ext',8) || ' ' ||
lpad('Unused',6) || ' ' ||
lpad('Free',4)
);
dbms_output.put_line(
rpad('-',15,'-') || ' ' ||
rpad('-',15,'-') || ' ' ||
rpad('-',28,'-') || ' ' ||
rpad('-',28,'-') || ' ' ||
lpad('-',8,'-') || ' ' ||
lpad('-',8,'-') || ' ' ||
lpad('-',6,'-') || ' ' ||
lpad('-',4,'-')
);
for r1 in c1 loop
dbms_space.free_blocks( 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 );
dbms_space.unused_space( 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 );
dbms_output.put_line( 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) || ' ' || lpad(to_char(m_on_free_list,'990'),4) ); end loop;
end; . / spool off