alter table deallocate unused ?

[ligang]

A trial :

1. create table ta ( a number , b number)
2. begin
for i in 1..400 loop
insert into ta values ( 1, 1);
commit;
end loop;
end;

3. select * from user_segments where segment_name = 'TA'
bytes blocks Extents
71680 35 4

4. delete from ta ;
commit;
select * from user_segments where segment_name = 'TA'

bytes blocks Extents
71680 35 4

--> the hwm is not reset

5. alter table ta deallocate unused
bytes blocks Extents
61440 30 4

To be very surprised , although I delete all the records then issue deallocate command , only 5 blocks space has been withdrawed . what happen to the left 30 blocks ? I guess it's a bug of oracle7.3 .

[Thomasps]

Hi

Try one more and answer yourself.!!

1. Truncate table ta;
2. select * from user_segments where segment_name = 'TA'


Thomas

[ligang]

Hi , Mr. Thomasps ,
sorry that you totally misunderstood what I mean .

Surely trancate will reset HWM and deallocate all the space (without issuing alter table deallocate command)

But What I mean is after issue delete form table plus issue

alter table deallocate command (do you know the meaning of deallocate ? Please check oracle manual ) , why the hwm is down aroudn 20% ( not 0 % and not 100% , do you think it is strange ?)

Please think again and welcome your reply .

something looks simply but actually not simply :-)

[peach_partner]

Hi,

alter table ta deallocate unused

The oracle server will deallocate all unused space above the HWM.

I think, your case means that the TA table have the 30 blocks of free spaces after delete and only 5 blocks of unused spaces.

[Thomasps]

SQL> select BYTES,BLOCKS,EXTENTS from user_segments where segment_name ='EMP';

BYTES BLOCKS EXTENTS
---------- ---------- ----------
2621440 640 11

SQL> analyze table emp compute statistics;
Table analyzed.
SQL> select BLOCKS,EMPTY_BLOCKS from user_tables where table_name='EMP';
BLOCKS EMPTY_BLOCKS
---------- ------------
579 60


(Ie 60 blocks are free)

SQL> delete from emp;
98304 rows deleted.
SQL> commit;
Commit complete.
SQL> alter table emp deallocate unused;
Table altered.
SQL> select BYTES,BLOCKS,EXTENTS from user_segments where segment_name ='EMP';
BYTES BLOCKS EXTENTS
---------- ---------- ----------
2375680 580 11


ie 640 - 60 = 580. Just released the empty blocks


Thomas

[ligang]

Thanks !

Oracle frees only unused space above the high water mark (that is, the point beyond which database blocks have not yet been formatted to receive data). Oracle deallocates unused space beginning from the end of the object and moving toward the beginning of the object to the high water mark.


http://lbdwww.epfl.ch/f/teaching/cou.../clauses4a.htm

[pando]

this j0nx is explained in docs, it's funny you suggest someone to read the doco and yourself dont do it

[ligang]

Hey , it's not that funny . I think this forum is the place to
let everyone review the technology , raise the issue and make big discussion and improve together . I do read document but as oracle is like a ocean , you are not able to explore every detail . like alter table
deallocate command , it is a confusing point I guess . Unless someone bring it on the table , I think quite a lot oracle guys are not so familar with it .

[pando]

hmm do you really read the doco, see what it says in SQL reference about this command in the FIRST paragraph

deallocate_unused_clause

Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the table, partition or subpartition, overflow data segment, LOB data segment, or LOB index and makes the space available for other segments in the tablespace. You can free only unused space above the high water mark (that is, the point beyond which database blocks have not yet been formatted to receive data)