1 2 Previous Next 24 Replies Latest reply: Nov 2, 2013 11:07 AM by Mark D Powell RSS

    How to know how much I can shrink the table

    SrinivasM.P.

      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

          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.

            I feel it should be there

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

              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

                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

                  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.

                    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

                      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

                        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

                          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

                            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

                              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

                                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

                                  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

                                    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