Reclaim Unused Space in Segments

I understand that Oracle can automatically allocate new extents for a segment when the segment requires more storage space. However, Oracle does not automatically release (deallocate) extents when a segment has a significant amount of unused space.

I am using this Report to give me information on a segment's High Water Mark:

SET SERVEROUTPUT ON;
 
DECLARE
 segment_owner VARCHAR2(100) := 'M104';
 segment_name VARCHAR2(100) := 'ORDERS';  
 segment_type VARCHAR2(100) := 'TABLE';
 total_blocks NUMBER;
 total_bytes NUMBER;
 unused_blocks NUMBER;
 unused_bytes NUMBER;
 last_used_extent_file_id NUMBER;
 last_used_extent_block_id NUMBER;
 last_used_block NUMBER;
BEGIN
 sys.dbms_space.unused_space(
  segment_owner,
  segment_name,
  segment_type,
  total_blocks,
  total_bytes,
  unused_blocks,
  unused_bytes,
  last_used_extent_file_id,
  last_used_extent_block_id,
  last_used_block );
 dbms_output.put_line('SEGMENT:       '||segment_owner||'.'||segment_name);
 dbms_output.put_line('TYPE:          '||segment_type);
 dbms_output.put_line('TOTAL BLOCKS:  '||total_blocks);
 dbms_output.put_line('TOTAL BYTES:   '||total_bytes);
 dbms_output.put_line('UNUSED BLOCKS: '||unused_blocks);
 dbms_output.put_line('UNUSED BYTES:  '||unused_bytes);
END;
/

SEGMENT:       M104.ORDERS
TYPE:          TABLE
TOTAL BLOCKS:  14
TOTAL BYTES:   114688
UNUSED BLOCKS: 6
UNUSED BYTES:  49152
 
PL/SQL procedure successfully completed.

Oracle documentation says that if you determine that a segment contains a significant number of unused blocks, you can attempt to deallocate one or more extents from the segment using the DEALLOCATE clause.

example:

SQL> SELECT extent_id, bytes
  2    FROM user_extents
  3   WHERE segment_name = 'ORDERS'
  4   ORDER BY extent_id;
 
 EXTENT_ID      BYTES
---------- ----------
         0      16384
         1      16384
         2      16384
         3      16384
         4      16384
         5      16384
         6      16384
 
7 rows selected.
 
SQL> ALTER TABLE orders
  2   DEALLOCATE UNUSED;
 
Table altered.
 
SQL> SELECT extent_id, bytes
  2    FROM user_extents
  3   WHERE segment_name = 'ORDERS'
  4   ORDER BY extent_id;
 
 EXTENT_ID      BYTES
---------- ----------
         0      16384
         1      16384
         2      16384
         3      16384


My question is this when I ran the above PL/SQL report against one of my tables in my PPSPROD enviroment.

SQL> show user
USER is "PPSPROD"
SQL> @C:\orant\sql\space.sql
SEGMENT:       PPSPROD.WORKS_OPERATION
TYPE:          TABLE
TOTAL BLOCKS:  69120
TOTAL BYTES:   141557760
UNUSED BLOCKS: 41616
UNUSED BYTES:  85229568

PL/SQL procedure successfully completed.

It showed UNUSED BLOCKS: 41616

When I checked the extent_id, bytes from user_extents I received this result
showing only one extent:

SQL> SELECT extent_id, bytes
  2  FROM user_extents
  3  WHERE segment_name = 'WORKS_OPERATION'
  4  ORDER BY extent_id;

EXTENT_ID     BYTES
--------- ---------
        0 141557760

I know I can't use ALTER TABLE DEALLOCATE UNUSED to deallocate one or more extents from the segment. What else can I use to release unused space in segments that show only one extent? The problem is that I have many of these segments with very high UNUSED BLOCKS but showing only one extent.



Thank you