alter table deallocate unused ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: alter table deallocate unused ?

  1. #1
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433

    alter table deallocate unused ?

    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 .
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Hi

    Try one more and answer yourself.!!

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


    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    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 :-)
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  4. #4
    Join Date
    Jan 2003
    Location
    Thailand
    Posts
    29
    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.
    Regards,
    P.Peach

  5. #5
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    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
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  6. #6
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    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
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    this j0nx is explained in docs, it's funny you suggest someone to read the doco and yourself dont do it

  8. #8
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    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 .
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width