RE: Deallocate unused (above high water mark)

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 12 May 2004 12:48:37 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF03B454B9@bosmail00.bos.il.pqe>

See:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/clau ses4a.htm#1004662

If it still doesn't make sense to you, reply with values for INITIAL, NEXT, and MINEXTENTS.

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"

-----Original Message-----
From: Sergei [mailto:good_morning_at_comcast.net]=20 Sent: Wednesday, May 12, 2004 12:36 PM
To: oracle-l_at_freelists.org
Subject: Deallocate unused (above high water mark)

Good morning Oracle gurus,

Could somebody please explain why after 'alter table deallocate unused'=20 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=3D'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=20 from user_tables where table_name=3D'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 =3D        256
Total bytes allocated to the table  =3D    2097152
Unused blocks (above HWM)           =3D         64
Unused bytes (above HWM)            =3D     524288
Last extent used file ID            =3D          9
Last extent used begining block ID  =3D        264
Last used block in last extent      =3D         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=3D'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=20 from user_tables where table_name=3D'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 =3D        192
Total bytes allocated to the table  =3D    1572864
Unused blocks (above HWM)           =3D          0
Unused bytes (above HWM)            =3D          0
Last extent used file ID            =3D          9
Last extent used begining block ID  =3D        264
Last used block in last extent      =3D         64

PL/SQL procedure successfully completed.

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

--=20
Sergei Shepelev,
Oracle DBA and instructor

What is in the script

unused_space.sql:

----------------------------- set serveroutput on -- declare=20 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 =3D '||lpad(tblock,10,' '));

dbms_output.put_line('Total bytes allocated to the table =3D '||lpad(tbyte,10,' '));

dbms_output.put_line('Unused blocks (above HWM) =3D '||lpad(ublock,10,' '));

dbms_output.put_line('Unused bytes (above HWM) =3D '||lpad(ubyte,10,' '));

dbms_output.put_line('Last extent used file ID =3D '||lpad(lue_fid,10,' '));

dbms_output.put_line('Last extent used begining block ID =3D '||lpad(lue_bid,10,' '));

dbms_output.put_line('Last used block in last extent =3D '||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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------

Received on Wed May 12 2004 - 11:47:22 CDT