1 Reply Latest reply on Jan 6, 2012 2:25 AM by Tubby

    compressed table using oracle advanced compression but space is not release

    909081
      Dear Experts,

      I am using oracle 11.2.0.2 with sap as application

      I have compressed one of my largest indetified table "SWWCNTP0" as per below statement

      Alter table sapsr3.SWWCNTP0 compress for oltp;

      After this i have performed reorg for the above table but it doesnt released the space

      before reorg 39gb
      after reorg 39g

      Please advice


      Regards
      Navin Somal
        • 1. Re: compressed table using oracle advanced compression but space is not release
          Tubby
          906078 wrote:
          Dear Experts,

          I am using oracle 11.2.0.2 with sap as application

          I have compressed one of my largest indetified table "SWWCNTP0" as per below statement

          Alter table sapsr3.SWWCNTP0 compress for oltp;

          After this i have performed reorg for the above table but it doesnt released the space

          before reorg 39gb
          after reorg 39g

          Please advice


          Regards
          Navin Somal
          How did you go about doing the reorg?

          He's a simple working example ...
          ME_ORCL?create table to_compress as
            2  select * from all_objects
            3  union all
            4  select * from all_objects
            5  union all
            6  select * from all_objects
            7  union all
            8  select * from all_objects
            9  union all
           10  select * from all_objects;
          
          Table created.
          
          Elapsed: 00:00:16.74
          ME_ORCL?select bytes
            2  from dba_segments
            3  where segment_name = 'TO_COMPRESS';
          
                       BYTES
          ------------------
                    44040192
          
          1 row selected.
          
          Elapsed: 00:00:00.06
          ME_ORCL?alter table to_compress compress for oltp;
          
          Table altered.
          
          Elapsed: 00:00:00.09
          
          --we don't expect any change here, because this only affects subsequent operations on the table, we need to MOVE the table to see any benefits in the existing data
          ME_ORCL?select bytes
            2  from dba_segments
            3  where segment_name = 'TO_COMPRESS';
          
                       BYTES
          ------------------
                    44040192
          
          1 row selected.
          
          Elapsed: 00:00:00.08
          ME_ORCL?alter table to_compress move compress for oltp;
          
          Table altered.
          
          Elapsed: 00:00:01.08
          ME_ORCL?select bytes
            2  from dba_segments
            3  where segment_name = 'TO_COMPRESS';
          
                       BYTES
          ------------------
                    14680064
          
          1 row selected.
          
          Elapsed: 00:00:00.02
          ME_ORCL?