2 Replies Latest reply: Dec 27, 2011 2:23 PM by Vishalp-Oracle RSS

    partition table compression

    sachinpawan
      hi all,

      i have one partition table and size is 25gb i want to reduce the size of the table using advance compression....

      how can i do it?
        • 1. Re: partition table compression
          864403
          Here is the command. Please note that use of advance compression requires additional licensing.

          ALTER TABLE mytable
          MOVE PARTITION mytable_part1
          COMPRESS;

          OR

          ALTER TABLE mytable
          MOVE PARTITION mytable_part1
          COMPRESS FOR ALL OPERATIONS;


          http://www.oracle.com/technetwork/database/options/compression/faq-092157.html

          Thanks
          • 2. Re: partition table compression
            Vishalp-Oracle
            http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables002.htm#ADMIN11630

            check table 20-1 to decide which compression would benefit.


            Compression and Partitioned Tables
            ========================
            A table can have both compressed and uncompressed partitions, and different partitions can use different compression methods. If the compression settings for a table and one of its partitions do not match, then the partition setting has precedence for the partition.

            To change the compression method for a partition, do one of the following:

            To change the compression method for new data only, use ALTER TABLE ... MODIFY PARTITION ... COMPRESS ...

            To change the compression method for both new and existing data, use either ALTER TABLE ... MOVE PARTITION ... COMPRESS ... or online table redefinition.