RE: Deallocate unused (above high water mark)
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 May 2004 12:48:37 -0400
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@xxxxxxxxxxx]=20
Sent: Wednesday, May 12, 2004 12:36 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
