What is the best way to remove compress basic from tables and partitions in production environment.
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).