Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, OSCAR.

Asked: September 29, 2017 - 8:34 pm UTC

Last updated: September 30, 2017 - 3:46 pm UTC

Version: oracle 11gr2 in exadata

Viewed 10K+ times! This question is

You Asked

What is the best way to remove compress basic from tables and partitions in production environment.

and Connor said...

If you just want to change the table so that new partitions are not compressed, you can do this:

SQL> @drop t

Y1                      Y2
----------------------- -------------------------
TABLE                   cascade constraints purge

1 row selected.


Table dropped.

SQL>
SQL> create table t ( x int )
  2   partition by range ( x )
  3   ( partition p1 values less than ( 1000 ),
  4    partition p2 values less than ( 2000 )
  5   )
  6   compress;

Table created.

SQL>
SQL> insert /*+ append */ into t
  2  select rownum from dual
  3  connect by level < 2000;

1999 rows created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> select  partition_name, compression, compress_for
  2  from user_tab_partitions
  3  where table_name = 'T';

PARTITION_NAME                 COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
P1                             ENABLED  BASIC
P2                             ENABLED  BASIC

2 rows selected.

SQL>
SQL> alter table t modify default attributes nocompress;

Table altered.

SQL>
SQL> alter table t add partition p3 values less than (3000);

Table altered.

SQL>
SQL> select  partition_name, compression, compress_for
  2  from user_tab_partitions
  3  where table_name = 'T';

PARTITION_NAME                 COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
P1                             ENABLED  BASIC
P2                             ENABLED  BASIC
P3                             DISABLED

3 rows selected.

SQL>
SQL>


If you want to uncompress existing partitions, you'll need to do "alter table move partition ..." which will lock those rows during the operation. If that is an issue, you would need to look at DBMS_REDEFINITION (plenty of examples on AskTOM for that).

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database