Oracle Procedure DBMS_SPACE.SPACE_USAGE

We were doing GL Purge. I wanted to see how much space has been freed up from GL Purge process. So i used following query

SELECT segment_name , to_char(TRUNC(SUM(blocks)*8/(1024)),'999,990') "Space in MB" FROM dba_extents WHERE segment_name IN ( 'GL_JE_HEADERS' , 'GL_IMPORT_REFERENCES' , 'GL_JE_BATCHES' , 'GL_BALANCES' , 'GL_JE_SEGMENT_VALUES' , 'GL_JE_LINES' ) GROUP BY segment_name ORDER BY 2 ; Result of above query did not change from what i got before purge. GL Purge will definitely not release freed up space. I wanted a general idea of what has happened to table blocks. I used procedure DBMS_SPACE.SPACE_USAGE to see how many blocks have what kind of free space. The script is as follows

DECLARE /* * * This script can give me how filled are the blocks of ia table and it's indexes * */ i NUMBER; TYPE t_chr_tbl IS table of varchar2(80) index by binary_integer; l_tables t_chr_tbl; l_indx NUMBER := 0; l_unformatted_blocks NUMBER; l_unformatted_bytes NUMBER; l_fs1_blocks NUMBER; l_fs2_blocks NUMBER; l_fs3_blocks NUMBER; l_fs4_blocks NUMBER; l_full_blocks NUMBER; l_fs1_bytes NUMBER; l_fs2_bytes NUMBER; l_fs3_bytes NUMBER; l_fs4_bytes NUMBER; l_full_bytes NUMBER; l_partition_name VARCHAR2(80); CURSOR c_tables IS SELECT owner , table_name FROM dba_tables WHERE table_name in ( 'GL_JE_HEADERS' , 'GL_IMPORT_REFERENCES' , 'GL_JE_BATCHES' , 'GL_BALANCES' , 'GL_JE_SEGMENT_VALUES' , 'GL_JE_LINES' ) ORDER BY owner, table_name ; CURSOR c_indexes (p_table VARCHAR2) IS SELECT owner, index_name FROM dba_indexes WHERE table_name = p_table ORDER BY owner, index_name ; PROCEDURE msg(p_msg VARCHAR2) IS BEGIN IF fnd_global.conc_request_id = -1 THEN dbms_output.put_line(p_msg); ELSE fnd_file.put_line(fnd_file.output, p_msg); END IF; END; --- --- --- PROCEDURE get_usage ( p_owner VARCHAR2 , p_segment VARCHAR2 , p_segment_type VARCHAR2 ) IS BEGIN DBMS_SPACE.SPACE_USAGE ( segment_owner => p_owner , segment_name => p_segment , segment_type => p_segment_type , unformatted_blocks => l_unformatted_blocks , unformatted_bytes => l_unformatted_bytes , fs1_blocks => l_fs1_blocks , fs1_bytes => l_fs1_bytes , fs2_blocks => l_fs2_blocks , fs2_bytes => l_fs2_bytes , fs3_blocks => l_fs3_blocks , fs3_bytes => l_fs3_bytes , fs4_blocks => l_fs4_blocks , fs4_bytes => l_fs4_bytes , full_blocks => l_full_blocks , full_bytes => l_full_bytes , partition_name => l_partition_name); msg(RPAD(p_segment,30,' ') || ' ' || to_char(l_unformatted_blocks, '999,999,999,999') || ' ' || to_char(l_fs1_blocks , '999,999,999,999') || ' ' || to_char(l_fs2_blocks , '999,999,999,999') || ' ' || to_char(l_fs3_blocks , '999,999,999,999') || ' ' || to_char(l_fs4_blocks , '999,999,999,999') || ' ' || to_char(l_full_blocks , '999,999,999,999') ); /* * * I do not understand purpose of bytes because bytes are smiply blocks multiplied by * block size * * msg(RPAD('.',30,' ') || ' ' || * to_char(l_unformatted_bytes , '999,999,999,999') || ' ' || * to_char(l_fs1_bytes , '999,999,999,999') || ' ' || * to_char(l_fs2_bytes , '999,999,999,999') || ' ' || * to_char(l_fs3_bytes , '999,999,999,999') || ' ' || * to_char(l_fs4_bytes , '999,999,999,999') || ' ' || * to_char(l_full_bytes , '999,999,999,999') * ); * */ END; BEGIN msg(' Blocks/Bytes Having'); msg('Segment UnFormatted Blocks 00-25% Free space 25-50% Free Space 50-75% Free Space 75-100% Free Space Full Blocks'); msg('==========================================================================================================================================='); FOR l_rec in c_tables LOOP get_usage(l_rec.owner, l_rec.table_name, 'TABLE'); FOR l_indx_rec in c_indexes(l_rec.table_name) LOOP get_usage(l_indx_rec.owner, l_indx_rec.index_name, 'INDEX'); END LOOP; END LOOP; END; /