1 2 Previous Next 19 Replies Latest reply: Jan 21, 2014 5:21 AM by User477708-OC RSS

    Basic compression on tablespace

    User477708-OC

      11.2.0.2.0


      I have a number of large tables that I can compress as theyll either have little or no DML going forward.  We have Enterprise edition but not the advanced compression licence so Im limited to Basic compression.  Ive read the docs and reviewed JLs note Compression in Oracle – Part 1: Basic Table Compression – All Things Oracle

      and I know how to create a table with basic compression and understand that only direct inserted data is compressed post compression.


      Question, Is it possible to put basic compression on a tablespace and add tables there as I see fit or is basic compression only at the table level?  I cant find a doc to say one or the other.

        • 1. Re: Basic compression on tablespace
          Srini Chavali-Oracle

          Compression only applies to tables, not tablespaces. You will have to individually compress tables as needed.

           

          HTH
          Srini

          • 2. Re: Basic compression on tablespace
            Aman....

            AFAIK It's only meant for tables not for tablespace.

             

            Aman....

            • 3. Re: Basic compression on tablespace
              Richard Harrison .

              Hi,

              I think it works - i've never used it this way (only on a per table basis) but the syntax allows it

               

              create tablespace test default compress; -- i'm using omf so no need for sizes or paths etc

               

              And thats on 10.2 before advanced compression even existed.

               

              Try it and see......

               

               

              Regards,

              Rich

              • 4. Re: Basic compression on tablespace
                Srini Chavali-Oracle

                Rich - I believe you are correct - learn something new every day :-)

                 

                CREATE TABLESPACE

                • 5. Re: Basic compression on tablespace
                  User477708-OC

                  rich is right, thanks man.  dunno why I didnt just test that, the important thing I wanted to see was BASIC in the compressed_for.

                   

                  create tablespace test_compress default compress;

                   

                   

                  create table t_comp

                  (col1 number)

                  tablespace test_compress;

                   

                   

                   

                  select compression, compress_for

                  from dba_tables

                  where table_name = 'T_COMP'

                   

                  COMPRESS COMPRESS_FOR

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

                  ENABLED  BASIC

                  • 6. Re: Basic compression on tablespace
                    Aman....

                    Thanks for the correction Rich , not testing didn't seem to be a good thing .

                     

                    Aman....

                    • 7. Re: Basic compression on tablespace
                      rp0428

                      Question, Is it possible to put basic compression on a tablespace and add tables there as I see fit or is basic compression only at the table level?  I cant find a doc to say one or the other.

                      Possible - yes and no. You marked this answer as correct:

                      I think it works - i've never used it this way (only on a per table basis) but the syntax allows it

                       

                      create tablespace test default compress; -- i'm using omf so no need for sizes or paths etc

                      And it is correct but there is one very important restriction with that answer. As the code says

                      This clause is not valid for a temporary tablespace or a locally managed tablespace.

                      Note the 'not valid' for a 'locally managed tablespace'.

                       

                      The issue is that best practice is to use locally managed tablespaces. See the High Availability Best Practices doc

                      http://docs.oracle.com/cd/B28359_01/server.111/b28282/configbp002.htm#i1007005

                      2.2.2.5 Use Locally Managed Tablespaces

                      Locally managed tablespaces perform better than dictionary-managed tablespaces, are easier to manage, and eliminate space fragmentation concerns. Locally managed tablespaces use bitmaps stored in the data file headers and, unlike dictionary managed tablespaces, do not contend for centrally managed resources for space allocations and de-allocations.

                      So make sure that your tablespaces follow the standards for your organization.

                       

                      Also make sure you document which tables should be compressed. If you copy the data or the table (e.g. using CTAS) the new data or table will NOT be compressed.

                      • 8. Re: Basic compression on tablespace
                        Richard Harrison .

                        Hi rp,

                        Where did you find that info - the test i did created a local one with compression on?

                         

                        SYS@>create tablespace test default compress;

                         

                         

                        Tablespace created.

                         

                         

                        SYS@>set pages 99

                        SYS@>select * from dba_tablespaces where tablespace_name='TEST'

                          2  /

                         

                         

                        TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS

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

                        MAX_EXTENTS   MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR

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

                        EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION   BIG PREDICA ENC

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

                        COMPRESS_FOR

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

                        TEST                                 8192          65536                       1

                        2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO

                        LOCAL      SYSTEM    NO  AUTO   ENABLED  NOT APPLY   NO  HOST    NO

                        BASIC

                         

                        Cheers,

                        Rich

                        • 9. Re: Basic compression on tablespace
                          Haafiz

                          Hi Richard,

                           

                          Thanks for the clarification through demo

                           

                          Regars,

                          Haafiz

                          • 10. Re: Basic compression on tablespace
                            rp0428
                            Thanks for the clarification through demo

                            CAUTION! Keep in mind that that 'demo' is for 10g; OP is using 11.2.0.2.0

                            • 11. Re: Basic compression on tablespace
                              User477708-OC

                              rp0428 wrote:

                               

                              Thanks for the clarification through demo

                              CAUTION! Keep in mind that that 'demo' is for 10g; OP is using 11.2.0.2.0

                               

                              > select version from v$instance;

                               

                               

                              VERSION

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

                              11.2.0.2.0

                               

                              -- tablespace I created in my demo earlier

                               

                              > select extent_management, compress_for from dba_tablespaces where tablespace_name='TEST_COMPRESS';

                               

                               

                              EXTENT_MAN COMPRESS_FOR

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

                              LOCAL      BASIC

                              • 12. Re: Basic compression on tablespace
                                rp0428
                                Where did you find that info

                                I found it in the SQL Language doc using the link that Srini provided. Just search for the word 'compress':

                                DEFAULT storage_clause

                                This clause lets you specify default storage parameters for all objects created in the tablespace and default compression of data for all tables created in the tablespace. This clause is not valid for a temporary tablespace or a locally managed tablespace.

                                For a dictionary-managed tablespace, you can specify the following storage parameters with this clause: COMPRESS, INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, and PCTINCREASE.

                                IMPORTANT NOTE: that is the 11.2 documentation. OP said this:

                                11.2.0.2.0

                                OP is using 11.2 so Srini's version of the doc applies.

                                • 13. Re: Basic compression on tablespace
                                  Srini Chavali-Oracle

                                  It looks like the documentation is incorrect. In 11.2.0.2 running on RHEL 5 (the Oracle Developer Days VM), I am able to successfully create a locally managed tablespace with the COMPRESS option

                                   

                                  create tablespace test datafile '/home/oracle/app/oracle/oradata/orcl/test01.dbf' size 100M extent management local default compress;

                                   

                                  And the DBA_TABLESPACES view for this TEST tablespace shows that EXTENT_MANAGEMENT is LOCAL, DEF_TAB_COMPRESSION is ENABLED and COMPRESS_FOR is BASIC.

                                   

                                  I will open a new thread in the Documentation forum to have this corrected

                                   

                                  Thanks

                                  Srini

                                  • 14. Re: Basic compression on tablespace
                                    jgarry

                                    It's also worth checking for bugs if you are going to use it.

                                    Doc 12417369.8

                                    Doc 1191474.1

                                    Bug 9707965

                                    Bug 10324526


                                     

                                     

                                    1 2 Previous Next