数据库空间有点紧张，暂时还无法扩容，所以把一些历史表进行压缩，以释放一些存储空间：

以CNDBA_2013为例，压缩之前是5806M。http://www.cndba.cn/dave/article/3698

| TABLE PARTITION | CD | CNDBA_2013 | 5806 |http://www.cndba.cn/dave/article/3698http://www.cndba.cn/dave/article/3698

http://www.cndba.cn/dave/article/3698

对于非分区表可以直接压缩：http://www.cndba.cn/dave/article/3698

alter table CNDBA_2013 move compress for archive High;http://www.cndba.cn/dave/article/3698

我们这里正好是分区表，需要单独对每个分区进行压缩：

#查询分区：
SQL>select SEGMENT_NAME , PARTITION_NAME, SEGMENT_TYPE , bytes/1024/1024||'M' as sz from user_segments where SEGMENT_NAME='CNDBA_2013'  order by 2 desc;

SEGMENT_NAME               PARTITION_NAME              SEGMENT_TYPE     SZ
------------------------------ ------------------------------ ------------------ -----------------------------------------
CNDBA_2013            P201312                  TABLE PARTITION     1716M
CNDBA_2013            P201311                  TABLE PARTITION     1416M
CNDBA_2013            P201310                  TABLE PARTITION     1145M
CNDBA_2013            P201309                  TABLE PARTITION     961M
CNDBA_2013            P201308                  TABLE PARTITION     552M
CNDBA_2013            P201307                  TABLE PARTITION     8M
CNDBA_2013            P201306                  TABLE PARTITION     8M


#Oracle 的压缩有不同级别，比如以下两种：
SQL> alter table CNDBA_2013 move partition P201311 compress for query High;
alter table CNDBA_2013 move partition P201311 compress for query High
            *
ERROR at line 1:
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type


SQL> alter table CNDBA_2013 move partition P201312 compress for archive High;
alter table CNDBA_2013 move partition P201312 compress for archive High
            *
ERROR at line 1:
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type

SQL> !oerr ora 64307
64307, 00000, "hybrid columnar compression is not supported for tablespaces on this storage type" 
// *Document: YES
// *Cause: An attempt was made to use hybrid columnar compression on unsupported storage.
// *Action: Create this table in a tablespace residing on Exadata, Oracle's Sun ZFS or Pillar storage or use a different compression type.
//
#这种列压缩只支持XD的环境，所以我们还是老老实实的用基本压缩：

SQL> alter table CNDBA_2013 move partition P201312 compress basic;
SQL> alter table CNDBA_2013 move partition P201311 compress basic;
SQL> alter table CNDBA_2013 move partition P201310 compress basic;
SQL> alter table CNDBA_2013 move partition P201309 compress basic;
SQL> alter table CNDBA_2013 move partition P201308 compress basic;

压缩之后空间：3886M，基本压缩差不多省了2G的磁盘空间：

| TABLE PARTITION | CD | CNDBA_2013 | 3886 |

http://www.cndba.cn/dave/article/3698

小知识点，随笔记之。

版权声明：本文为博主原创文章，未经博主允许不得转载。