Oracle View Object Space usage show_space__oracle

Tom Great God wrote one for viewing Oracle database object space usage.
The following shows how to use:

– Tools Source

CREATE OR REPLACE PROCEDURE show_space (p_segname in VARCHAR2, P_owner in Varc
                                       HAR2 default USER, P_type in VARCHAR2 default ' TABLE ',
  P_partition in VARCHAR2 DEFAULT NULL) as l_free_blks number;
  L_total_blocks number;
  L_total_bytes number;
  L_unused_blocks number;
  L_unused_bytes number;
  L_lastusedextfileid number;
  L_lastusedextblockid number;
  L_last_used_block number;
  L_segment_space_mgmt VARCHAR2 (255);
  L_unformatted_blocks number;
  L_unformatted_bytes number;
  L_fs1_blocks number;
  L_fs1_bytes number;
  L_fs2_blocks number;
  L_fs2_bytes number;
  L_fs3_blocks number;
  L_fs3_bytes number;
  L_fs4_blocks number;
  L_fs4_bytes number;
  L_full_blocks number;

  L_full_bytes number;
     PROCEDURE p (P_label in VARCHAR2,         P_num in number) is BEGIN dbms_output.put_line (Rpad (P_label, 40, '. ') | |
  To_char (P_num, ' 999,999,999,999 '));
End; BEGIN EXECUTE IMMEDIATE ' select ts.segment_space_management from dba_segments seg,dba_tablespaces ts where seg.segment_n Ame =:p _segname and (:p _partition is null or seg.partition_name =:p _partition) and Seg.owner =:p _owner and Seg.tablespa

  Ce_name=ts.tablespace_name ' into L_segment_space_mgmt USING p_segname, P_partition, P_partition, P_owner; --Exception--When Too_many_rows--then--dbms_output.put_line (' This Mus
  t be a partitioned table,use p_partition => ');
  --return;

  --end;
                           IF l_segment_space_mgmt = ' AUTO ' THEN dbms_space.space_usage (P_owner, P_segname, P_type, L_unformatted_blocks, L_unformatted_bytes, L_fs1_blocks, L_fs1_bytes, L_fs2_blocks, L_fs2_bytes, L_fs3_blocks, l_fs3_byTES, L_fs4_blocks, L_fs4_bytes, L_full_blocks, L_full_bytes, P_parti

    tion);
    P (' Unformatted Blocks ', l_unformatted_blocks);
    P (' FS1 Blocks (0-25) ', l_fs1_blocks);
    P (' FS2 Blocks (25-50) ', l_fs2_blocks);
    P (' FS3 Blocks (50-75) ', l_fs3_blocks);
    P (' FS4 Blocks (75-100) ', l_fs4_blocks);
  P (' Full Blocks ', l_full_blocks); ELSE dbms_space.free_blocks (Segment_owner => p_owner, segment_name => p_segname, SEGM
  Ent_type => p_type, freelist_group_id => 0, free_blks =>);

  End IF; Dbms_space.unused_space (Segment_owner => p_owner, segment_name => p_segname, Segment_type
                          => P_type, Partition_name => p_partition, Total_blocks =>, Total_bytes => l_total_bytes, unused_blocks => L_UNUSED_BLOcks, Unused_bytes => l_unused_bytes, last_used_extent_file_id =&gt ;
                          L_lastusedextfileid, last_used_extent_block_id => L_lastusedextblockid,

  Last_used_block => L_last_used_block);
  P (' Total Blocks ', l_total_blocks);    
  P (' Total Bytes ', l_total_bytes);
  P (' Total MBytes ', trunc (l_total_bytes/1024/1024));
  P (' Unused Blocks ', l_unused_blocks);
  P (' Unused Bytes ', l_unused_bytes);
  P (' Last Used Ext Fileid ', L_lastusedextfileid);
  P (' Last Used Ext blockid ', l_lastusedextblockid);
P (' last Used block ', l_last_used_block); End;

– Usage Demo

CREATE TABLE Test_space
as
select * from Dba_tables;
/

sys@orcl> exec show_space (' test_space ');

Total Blocks .................. 112
Total Bytes .................. 917,504
Total MBytes .................. 0
Unused Blocks ......... .......... ... 3
Unused Bytes ......... .......... ... 24,576
Last Used Ext Fileid ............ 1
Last Used Ext blockid ............ 115,608
Last Used blocks ................ ... 5

The PL/SQL process has completed successfully.

Sys@orcl> Delete from Test_space;

3044 rows have been deleted.

Sys@orcl> commit;

Submit completed.

sys@orcl> exec show_space (' test_space ');

Total Blocks .................. 112
Total Bytes .................. 917,504
Total MBytes .................. 0
Unused Blocks ......... .......... ... 3
Unused Bytes ......... .......... ... 24,576
Last Used Ext Fileid ............ 1
Last Used Ext blockid ............ 115,608
Last Used blocks ................ ... 5

The PL/SQL process has completed successfully.

sys@orcl> truncate TABLE test_space;

The table was truncated.

sys@orcl> exec show_space (' test_space ');
Total Blocks .................. 8
Total Bytes .................. 65,536
Total MBytes .................. 0
Unused Blocks ......... .......... ... 7
Unused Bytes ......... .......... ... 57,344
Last Used Ext Fileid ............ 1
Last Used Ext blockid ............ 101,872
Last Used blocks ................ ... 1

The PL/SQL process has completed successfully.

drop table test_space;