1. You can execute the following process in PL/SQL test window.
Declare
Owner varchar2 (30 );
Name varchar2 (30 );
Seg_type varchar2 (30 );
Tblock number;
Tbyte number;
Ublock number;
Ubyte number;
Lue_fid number;
Lue_bid number;
Lublock number;
Begin
Dbms_space.unused_space (upper ('Scott '), upper ('emp'), 'table', tblock, tbyte, ublock, ubyte, lue_fid, lue_bid, lublock );
Dbms_output.put_line ('total blocks allocated to table = '| to_char (tblock ));
Dbms_output.put_line ('total bytes allocated to table = '| to_char (tbyte ));
Dbms_output.put_line ('unused blocks (above hwm) = '| to_char (ublock ));
Dbms_output.put_line ('unused bytes (above hwm) = '| to_char (ubyte ));
Dbms_output.put_line ('Last extent used file ID = '| to_char (lue_fid ));
Dbms_output.put_line ('Last extent used beginning block id = '| to_char (lue_bid ));
Dbms_output.put_line ('Last used block in last extent = '| to_char (lublock ));
End;
----------------------------------------------
Total blocks allocated to table = 8
Total Bytes allocated to table = 65536
Unused blocks (above hwm) = 6
Unused bytes (above hwm) = 49152
Last extent used file ID = 1
Last extent used beginning blocks id = 50465
Last used block in last extent = 2
2. You can collect statistics on tables and view dba_tables.
3. Processing hwm
A) exp/drop/IMP table
B) ALTER TABLE XXX move, which requires twice the space during the move Process
C) alter table xxx deallocate unused; process idle space above hwm