This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Nov 2, 2013 9:07 AM by Mark D Powell RSS

How to know how much I can shrink the table

SrinivasM.P. Newbie
Currently Being Moderated

How can I know how much I can shrink the table after firing the delete statement?

  • 1. Re: How to know how much I can shrink the table
    JohnWatson Guru
    Currently Being Moderated

    You have no control over how much the table is shrunk.  Your ALTER TABLE...SHRINK SPACE command will reduce the segment size by the maximum possible.

    --

    John Watson

    Oracle Certified Master DBA

  • 2. Re: How to know how much I can shrink the table
    SrinivasM.P. Newbie
    Currently Being Moderated

    I feel it should be there

  • 3. Re: How to know how much I can shrink the table
    Anar Godjaev Expert
    Currently Being Moderated

    Hi,

     

    alter table table_name shrink space;

     

    For example:

     

    create table HWM_TEST(

      ID      NUMBER,

      DB_USERVARCHAR2(12),

      TARIH   DATE

      )

      STORAGE(

              MAXEXTENTS       UNLIMITED

             );

     

    insert into HWM_TEST

      select level   id,

              user    db_user,

              sysdate tarih

       from dual

      connect by level<100001;

      /

      commit;

      /

      100000
    rows created.

      Commit complete.

     

      insert into HWM_TEST

      select * from HWM_TEST;

      /

      insert into HWM_TEST

      select * from HWM_TEST;

      /

      insert into HWM_TEST

      select * from HWM_TEST;

      /

      insert into HWM_TEST

      select * from HWM_TEST;

      /

      insert into HWM_TEST

      select * fromHWM_TEST;

      /

      commit;

      100000
    rows created.

      200000
    rows created.

      400000
    rows created.

      800000
    rows created.

      1600000 rows created.

      Commit complete.

     

     

     

    select count(*) from hwm_test;

       COUNT(*)

      ----------

    3200000

     

       1 row selected.

      Elapsed: 00:00:02.26

     

     

     

    delete from HWM_TEST;

    /

      commit;

     

      COUNT(*)

    ----------

    0

      1 row selected.

      Elapsed: 00:00:02.42

     

      alter table HWM_TEST enable row movement;

      alter table HWM_TEST shrink space;

      set timing on

      select count(*) from hwm_test;

     

     

    COUNT(*)

    ----------

    0

       1 row selected.

      Elapsed: 00:00:00.01

     

    Thank you

     

     

     

     

     

     

     

     

  • 4. Re: How to know how much I can shrink the table
    John Stegeman Oracle ACE
    Currently Being Moderated

    What you feel and what is reality don't necessarily have to correspond.

  • 5. Re: How to know how much I can shrink the table
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    One more inf. if shrink could take longer time, recommended do this in 2 steps

    alter table ... shrink space COMPACT;

    alter table ... shrink space CASCADE;


    • The COMPACT clause lets you divide the shrink segment operation into two phases. When you specify COMPACT, Oracle Database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the deallocation of the space until a future time. This option is useful if you have long-running queries that might span the operation and attempt to read from blocks that have been reclaimed. The defragmentation and compaction results are saved to disk, so the data movement does not have to be redone during the second phase. You can reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete the second phase.

     

    ----

    Ramin Hashimzade

  • 6. Re: How to know how much I can shrink the table
    SrinivasM.P. Newbie
    Currently Being Moderated

    before firing following statements I want to know! how much it can shrink? what is the impact of server performance?

     

      alter table HWM_TEST enable row movement;

      alter table HWM_TEST shrink space;

  • 7. Re: How to know how much I can shrink the table
    Taral Journeyer
    Currently Being Moderated

    You can try using segment advisor which can show you how much approximately you can save (in numbers). Here is the link

     

    Reclaiming Wasted Space

     

    Here is the short output from documentation

     

    select tablespace_name, segment_name, segment_type, partition_name,

    recommendations, c1 from

    table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));

     

     

    TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE

    ------------------------------ ------------------------------ --------------

    PARTITION_NAME

    ------------------------------

    RECOMMENDATIONS

    -----------------------------------------------------------------------------

    C1

    -----------------------------------------------------------------------------

    TVMDS_ASSM ORDERS1 TABLE PARTITION

    ORDERS1_P2

    Perform shrink, estimated savings is 57666422 bytes.

    alter table "STEVE"."ORDERS1" modify partition "ORDERS1_P2" shrink space

     

    TVMDS_ASSM ORDERS1 TABLE PARTITION

    ORDERS1_P1

    Perform shrink, estimated savings is 45083514 bytes.

    alter table "STEVE"."ORDERS1" modify partition "ORDERS1_P1" shrink space

     

    TVMDS_ASSM_NEW ORDERS_NEW TABLE

     

    But real question is why and what is the need of it.

  • 8. Re: How to know how much I can shrink the table
    Anar Godjaev Expert
    Currently Being Moderated

    HI ,

     

    Thank you for response:

     

    You can easily know how much you  can shrink if you try segment advisor. All required is true statistics. Off course Performance is a good thing. OLTP transactions will work faster.

     

    Thank you

  • 9. Re: How to know how much I can shrink the table
    EdStevens Guru
    Currently Being Moderated

    SrinivasM.P. wrote:

     

    How can I know how much I can shrink the table after firing the delete statement?

     

    And what business or technical decision will be made based on the answer?  What will you do differently if the answer is "42" vs "128"?

     

     

    If I delete 1000 rows, I can usually assume that at some point in the near future, I'll be inserting a new 1000 rows.  Not all at once, perhaps, but eventually.  Why bother to shrink the table, if I'm just going to have to ask for the space again?

  • 10. Re: How to know how much I can shrink the table
    jgarry Guru
    Currently Being Moderated

    Why will OLTP transactions work faster if you shrink the table?

  • 11. Re: How to know how much I can shrink the table
    Anar Godjaev Expert
    Currently Being Moderated

    HI ,

     

    Thank you for question:

    Try to answer this question.

    It depends from many things. There are cases where compressing data in blocks could make some inserts/updates slower . E.g. Oracle will need to split data block

    This is an expensive operation.

     

    Thank you

  • 12. Re: How to know how much I can shrink the table
    TSharma-Oracle Guru
    Currently Being Moderated

    Are you saying shrink will compress data in blocks? really?

     

    i hope this is not your another try by creating self made user and get some points.

     

    I hope everybody checks the following link to know  what are you doing exactly (how yo are creating users and getting point). What is your purpose of getting points like this?

     

    RMAN ERROR - 06428

    ORA-01548: active rollback segment

  • 13. Re: How to know how much I can shrink the table
    jgarry Guru
    Currently Being Moderated

    TSharma is too easy on you.  You simply don't know what you are talking about.

  • 14. Re: How to know how much I can shrink the table
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    Hi All.

    Of course SHRINK "never never" compress data in the blocks. Might be Anar confused "Advanced Compression for OLTP" .

    Shrink only frees up space it is recommended do it for example when you have big LOB segments and you want to frees it up because you will not need it in the future. Or you have problem with space on disk and you have to deleted oldest records and frees up space . but you must think about if your database in future also will take the place of again it is not recommended frees it up ,why? because oracle will need increase space under table space again and it is additional operation for oracle )))

     

    ----

    Ramin Hashimzade

1 2 Previous Next

Incoming Links

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points