Deallocate unused (above high water mark)
- From: Sergei <good_morning@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 12 May 2004 11:36:08 -0500
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@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:
