Deallocate unused (above high water mark)

Good morning Oracle gurus,

Could somebody please explain why after 'alter table deallocate unused' my user_tables view still shows empty blocks? At the same time, dbms_space.unused_space procedure shows 0 empty blocks.

Thanks,

Sergei.
Oracle DBA and instructor.

That's what happened:

SQL> analyze table test compute statistics; Table analyzed.

SQL> select extent_id, bytes from user_extents where segment_name='TEST';  EXTENT_ID BYTES
---------- ----------

         0      65536
         1      65536
         2      65536
         3      65536
         4      65536
         5      65536
         6      65536
         7      65536
         8      65536
         9      65536
        10      65536
        11      65536
        12      65536
        13      65536
        14      65536
        15      65536
        16    1048576

17 rows selected.

SQL> select num_rows, blocks, num_rows/blocks as "R/B", empty_blocks from user_tables where table_name='TEST';   NUM_ROWS BLOCKS R/B EMPTY_BLOCKS ---------- ---------- ---------- ------------

      1000 180 5.55555556 76

1 row selected.

(the

    unused_space.sql
script runs

    dbms_space.unused_space
procedure. Details are at the bottom.)

SQL> @unused_space
Enter value for owner: jh
Enter value for table_name: test

Total blocks allocated to the table =        256
Total bytes allocated to the table  =    2097152
Unused blocks (above HWM)           =         64
Unused bytes (above HWM)            =     524288
Last extent used file ID            =          9
Last extent used begining block ID  =        264
Last used block in last extent      =         64

PL/SQL procedure successfully completed.

SQL> alter table test deallocate unused; Table altered.

SQL> analyze table test compute statistics; Table analyzed.

SQL> select extent_id, bytes from user_extents where segment_name='TEST';  EXTENT_ID BYTES
---------- ----------

         0      65536
         1      65536
         2      65536
         3      65536
         4      65536
         5      65536
         6      65536
         7      65536
         8      65536
         9      65536
        10      65536
        11      65536
        12      65536
        13      65536
        14      65536
        15      65536
        16     524288

17 rows selected.

(The last extent did get a cut)

SQL> select num_rows, blocks, num_rows/blocks as "R/B", empty_blocks from user_tables where table_name='TEST';   NUM_ROWS BLOCKS R/B EMPTY_BLOCKS ---------- ---------- ---------- ------------

      1000 180 5.55555556 12

1 row selected.

(Some empty blocks are gone, but not all)

SQL> @unused_space
Enter value for owner: jh
Enter value for table_name: test

Total blocks allocated to the table =        192
Total bytes allocated to the table  =    1572864
Unused blocks (above HWM)           =          0
Unused bytes (above HWM)            =          0
Last extent used file ID            =          9
Last extent used begining block ID  =        264
Last used block in last extent      =         64

PL/SQL procedure successfully completed.

( !!!!! All blocks above high water mark are gone, according to
dbms_space !!!!!)

-- 
Sergei Shepelev,
Oracle DBA and instructor


What is in the script
	unused_space.sql:

-- -----------------------------
set serveroutput on
--
declare 
	tblock	number;
	tbyte	number;
	ublock	number;
	ubyte	number;
	lue_fid	number;
	lue_bid	number;
	lublock	number;
--
begin
	dbms_space.unused_space(
		upper('&owner'),
		upper('&table_name'),
		'TABLE',
		tblock,
		tbyte,
		ublock,
		ubyte,
		lue_fid,
		lue_bid,
		lublock);

	dbms_output.put_line('Total blocks allocated to the table = '||lpad(tblock,10,' '));
	dbms_output.put_line('Total bytes allocated to the table  = '||lpad(tbyte,10,' '));
	dbms_output.put_line('Unused blocks (above HWM)           = '||lpad(ublock,10,' '));
	dbms_output.put_line('Unused bytes (above HWM)            = '||lpad(ubyte,10,' '));
	dbms_output.put_line('Last extent used file ID            = '||lpad(lue_fid,10,' '));
	dbms_output.put_line('Last extent used begining block ID  = '||lpad(lue_bid,10,' '));
	dbms_output.put_line('Last used block in last extent      = '||lpad(lublock,10,' '));
end;
/
-- -------------------------------



----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------