Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2008
    Posts
    38

    Unanswered: alter table move VS shrink space

    I tested the alter table move and shrınk clause.I notıce that after the alter table move clause, empty_blocks do not change.I dont understand why??


    analyze table esasbranch.temphistory compute statistics

    select table_name,a.blocks,b.bytes/1024 as KB,num_rows,empty_blocks
    from user_tables a, user_segments b
    where a.table_name = b.segment_name
    and empty_blocks >0
    order by empty_blocks desc

    Table_name Blocks KB Num_rows Empty_blocks
    ------------------- --------- ----- ------------ ------------------
    TEMPHISTORY 0 52224 0 6528


    alter table esasbranch.temphistory move
    analyze table esasbranch.temphistory compute statistics

    select table_name,a.blocks,b.bytes/1024 as KB,num_rows,empty_blocks
    from user_tables a, user_segments b
    where a.table_name = b.segment_name
    and empty_blocks >0
    order by empty_blocks desc

    Table_name Blocks KB Num_rows Empty_blocks
    ------------------- --------- ----- ------------ ------------------
    TEMPHISTORY 0 52224 0 6528


    ALTER TABLE esasbranch.temphistory enable row movement

    ALTER TABLE esasbranch.temphistory shrink space

    analyze table esasbranch.temphistory compute statistics

    select table_name,a.blocks,b.bytes/1024 as KB,num_rows,empty_blocks
    from user_tables a, user_segments b
    where a.table_name = b.segment_name
    and empty_blocks >0
    order by empty_blocks desc

    Table_name Blocks KB Num_rows Empty_blocks
    ------------------- --------- ----- ------------ ------------------
    TEMPHISTORY 0 64 0 8

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    ALTER TABLE MOVE doesn't reset empty_blocks, it will reset the HWM. That is, if your table has, for example, 16 used blocks, but only 12 of them contains data (think "delete", "insert", "update"), if you supply an ALTER TABLE MOVE the HWM after that will be again 12. However, empty_blocks always show how many of the allocated blocks for the segment have never been used (above the HWM). That's why when you submit a SHRINK space that space can be reclaimed because it is above the HWM.

  3. #3
    Join Date
    Feb 2008
    Posts
    38
    thanks martinez,
    how about shrink statement?
    does it reclaim space below HWM and above HWM as well?

  4. #4
    Join Date
    Feb 2008
    Posts
    38
    how does the shrink reset empty blocks?
    What is the diffrence between shrink and alter table move

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I believe this is better explained with an example.
    Code:
    SQL>
    SQL> create table t ( x number )
      2  tablespace users
      3  storage ( initial 10M next 10M )
      4  /
    
    Table created.
    
    SQL> analyze table t compute statistics;
    
    Table analyzed.
    
    SQL> select blocks, extents from user_segments where segment_name = 'T';
    
        BLOCKS    EXTENTS
    ---------- ----------
          1280         10
    
    SQL> select blocks, empty_blocks from user_tables where table_name = 'T';
    
        BLOCKS EMPTY_BLOCKS
    ---------- ------------
             0         1280
    So, I started creating a table named T and requested initially 10mb allocated, which turned out to be 1280 blocks and 10 extents. From there you can see:
    - table T has 1280 blocks allocated (blocks in user_segment)
    - none of which are *formatted* to receive data (blocks in user_tables)

    Then, I insert some data
    Code:
    SQL> insert into t
      2  select rownum
      3    from dual
      4  connect by level <= 100000;
    
    100000 rows created.
    
    SQL> analyze table t compute statistics;
    
    Table analyzed.
    
    SQL> select blocks, extents from user_segments where segment_name = 'T';
    
        BLOCKS    EXTENTS
    ---------- ----------
          1280         10
    
    SQL> select blocks, empty_blocks from user_tables where table_name = 'T';
    
        BLOCKS EMPTY_BLOCKS
    ---------- ------------
           186         1094
    I inserted 100,000 rows, from there you can see:
    - allocated blocks/extents for the table did not change
    - however, blocks formated to receive data were raised by 186 and the remaining blocks are empty

    186 blocks are the HWM now, because those are the blocks that sometime were formatted to receive data. Blocks above 186 are allocated blocks which have never been formatted to receive data.

    I will delete some data now to show you it will not raise empty_blocks nor it will lower the blocks that are formatted to receive data (that is, the HWM).
    Code:
    SQL> delete from t where rownum <= 90000;
    
    90000 rows deleted.
    
    SQL> analyze table t compute statistics;
    
    Table analyzed.
    
    SQL> select blocks, empty_blocks from user_tables where table_name = 'T';
    
        BLOCKS EMPTY_BLOCKS
    ---------- ------------
           186         1094
    See, the delete did nothing to change the HWM, but..
    Code:
    SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from t;
    
    USED_BLOCKS
    -----------
             16
    tells me only 16 of those 186 contains data. The rest blocks belong to the segment's freelist to be used for inserts/updates.

    Now, I will *move* the table to show you how it will re-adjust the HWM.
    Code:
    SQL> alter table t move tablespace users;
    
    Table altered.
    
    SQL> analyze table t compute statistics;
    
    Table analyzed.
    
    SQL> select blocks, empty_blocks from user_tables where table_name = 'T';
    
        BLOCKS EMPTY_BLOCKS
    ---------- ------------
            20         1260
    See, it shrinked down the HWM to just 20 from 86 and raised the empty_blocks, but..
    Code:
    SQL> select blocks, extents from user_segments where segment_name = 'T';
    
        BLOCKS    EXTENTS
    ---------- ----------
          1280         10
    tells you it did nothing to *shrink* the allocated space asigned to the segment, meaning that at this stage the segment will still be using, at the operating system level, the same kind of storage. Now, to *reclaim* that space we will use shrink.
    Code:
    SQL> alter table t enable row movement;
    
    Table altered.
    
    SQL> alter table t shrink space;
    
    Table altered.
    
    SQL> analyze table t compute statistics;
    
    Table analyzed.
    
    SQL> select blocks, extents from user_segments where segment_name = 'T';
    
        BLOCKS    EXTENTS
    ---------- ----------
           128          1
    
    SQL> select blocks, empty_blocks from user_tables where table_name = 'T';
    
        BLOCKS EMPTY_BLOCKS
    ---------- ------------
            20          108
    
    SQL>
    There, you see the table actually shrinked down from 1280 blocks allocated and 10 extents, to its minimum, 128 blocks and just 1 extent.

    I hope it has cleared your doubts.

  6. #6
    Join Date
    Feb 2008
    Posts
    38
    thanks a lot for this good explanation
    1-)After big deletion, which one do you advice? shrink or alter table move, or both of them?
    2-)Is there any advantage to shrink the allocated space asigned to the segment?
    3-)Does the shrink command also shrinked down the HWM ?

    Thanks again
    Last edited by tjay83; 02-08-08 at 15:19.

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It depends.

    1) You tell us. Will the segment be again at the same size it was before the delete ? Have you determined its grow tendency ? How many extents are allocated daily/weekly/monthly ? It all depends. You probably don't want to shrink or move it after all.

    2) Again, depends. There is the advantage that you could probably reclaim that space to the operating system, but then there is the disadvantage that if this segment ever needs to get to the same size again you will be requesting extents again.

    3) It request both, below and above HWM space.

  8. #8
    Join Date
    Feb 2008
    Posts
    38
    thanks for ur help martinez.
    My last question is;
    1-) is there any short script to find free blocks below HWM?
    2-) After using shrink command, I think I dont need to use "alter table <tablename>deallocated unused" clause.

    Thanks again
    Last edited by tjay83; 02-09-08 at 05:01.

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    1) I just gave you one. Read above. Actually, that will work if row chaining is not taking place and the segment resides in just one datafile.

    2) No. Shrink = below AND above HWM. Deallocate unsued = above HWM only.

  10. #10
    Join Date
    Feb 2008
    Posts
    38
    thanks martinez, I have one more question for shrinking indexes.

    In order to consider indexes; Does the following statement return the unused space inside index tablespace for use in other segments? or only compact the index segment?

    alter table <table_name> shrink space cascade;
    Last edited by tjay83; 02-10-08 at 09:14.

  11. #11
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Please, test and see (that's what I would have done).

    Most of your questions are answered on the documentation.

  12. #12
    Join Date
    Feb 2008
    Posts
    38
    ok martinez,thanks again
    sorry for any inconvinience

Posting Permissions

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