Compress old partition data in Oracle 11g

Compress old partition data in Oracle 11g (Range partition)

SQL> select table_name,tablespace_name,partition_name,num_rows,compression from dba_tab_partitions where table_name='COMP_TEST';

TABLE_NAME        TABLESPACE_NAME       PARTITION_NAME        NUM_ROWS COMPRESS

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

COMP_TEST        TBS01       P1 917504 DISABLED

COMP_TEST        TBS02       P2 917504 DISABLED

COMP_TEST        TBS03       P3 458752 DISABLED

Elapsed: 00:00:00.00

SQL> select tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name like '%TBS0%';

TABLESPACE_NAME        BYTES/1024/1024

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

TBS01        55.6875

TBS02        55.6875

TBS03 33.625

TBS04TEST      2

TBS05TEST      2

TBS06      2

6 rows selected.

Elapsed: 00:00:00.01

SQL> select table_name,index_name,index_type,status from dba_indexes where table_name='COMP_TEST';

TABLE_NAME        INDEX_NAME       INDEX_TYPE   STATUS

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

COMP_TEST        GLOBALIND       NORMAL   VALID

COMP_TEST        LOCALIND       NORMAL   N/A

Elapsed: 00:00:00.05

SQL> 

SQL> 

SQL> alter table comp_test move partition p1 tablespace tbs04test compress update global indexes;

Table altered.

Elapsed: 00:01:10.15

SQL> 

SQL> 

SQL>  select table_name,index_name,index_type,status from dba_indexes where table_name='COMP_TEST';

TABLE_NAME        INDEX_NAME       INDEX_TYPE   STATUS

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

COMP_TEST        GLOBALIND       NORMAL   VALID

COMP_TEST        LOCALIND       NORMAL   N/A

Elapsed: 00:00:00.02

SQL> SQL> select table_name,tablespace_name,partition_name,num_rows,compression from dba_tab_partitions where table_name='COMP_TEST';

TABLE_NAME        TABLESPACE_NAME       PARTITION_NAME        NUM_ROWS COMPRESS

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

COMP_TEST        TBS04TEST       P1 917504 ENABLED

COMP_TEST        TBS02       P2 917504 DISABLED

COMP_TEST        TBS03       P3 458752 DISABLED

Elapsed: 00:00:00.01

SQL> SQL> select tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name like '%TBS0%';

TABLESPACE_NAME        BYTES/1024/1024

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

TBS01        55.6875

TBS02 57.75

TBS03 33.625

TBS04TEST     18

TBS05TEST      2

TBS06      2

6 rows selected.

Elapsed: 00:00:00.01

SQL> SQL> 

SQL> 

SQL> select segment_name,tablespace_name from dba_segments where tablespace_name='TBS01';

SEGMENT_NAME   TABLESPACE_NAME

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

LOCALIND   TBS01

Elapsed: 00:00:00.06

SQL> alter index LOCALIND rebuild partition p1 tablespace TBS04TEST;

Index altered.

Elapsed: 00:00:15.08

SQL> 

SQL> 

SQL> 

SQL>  select segment_name,tablespace_name from dba_segments where tablespace_name='TBS01';

no rows selected

Elapsed: 00:00:00.02

SQL> 

SQL> 

SQL> select tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name like '%TBS0%';

TABLESPACE_NAME        BYTES/1024/1024

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

TBS01        55.6875

TBS02 57.75

TBS03 33.625

TBS04TEST 38.875

TBS05TEST      2

TBS06      2

6 rows selected.

Elapsed: 00:00:00.00

SQL> SQL> 

SQL> 

SQL> alter database datafile '/u02/datafiles/tbs01.dbf' resize 2m;

Database altered.

Elapsed: 00:00:00.06

SQL> 

SQL> 

SQL> select tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name like '%TBS0%';

TABLESPACE_NAME        BYTES/1024/1024

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

TBS01      2

TBS02 57.75

TBS03 33.625

TBS04TEST 38.875

TBS05TEST      2

TBS06      2

6 rows selected.

Elapsed: 00:00:00.00

SQL>