oracle index压缩,oracle表及索引压缩

系统内发现应用人员备份的两张历史表，通过压缩释放了近30GB的空间。

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> alter table TB_CNB_PAYCOMPONENTRECORD_2009 compress move tablespace users parallel;

Table altered.

压缩前：

OWNER     TABLESPACE_NAME     SEGMENT_NAME                         SEGMENT_TYPE               GB

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

TBSG       TB30            TB_CNB_PAYCOMPONENTRECORD_2009    TABLE              7.11035156

Tablespace        Used MB     Free MB    Total MB   Pct Free

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

TB30              116,553         127     116,680          0

压缩后，表释放空间4GB：

Tablespace        Used MB     Free MB    Total MB   Pct Free

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

TB30              109,272       7,408     116,680          6

OWNER     TABLESPACE_NAME     SEGMENT_NAME                         SEGMENT_TYPE        GB

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

TBSG        USERS           TB_CNB_PAYCOMPONENTRECORD_2009             TABLE               3.0012207

表上的索引压缩后，空间释放1G。

tbsg@HRDB> analyze index IND_PAYDATEID validate structure;

Index analyzed.

tbsg@HRDB> select PCT_USED,OPT_CMPR_COUNT,OPT_CMPR_PCTSAVE from index_stats;

PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

90              1               38

压缩前：

OWNER                          TABLESPACE_NAME                SEGMENT_NAME                   SEGMENT_TYPE           GB

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

TBSG                           TB30                           IND_PAYDATEID                  INDEX           3.125

压缩后：

OWNER                          TABLESPACE_NAME                SEGMENT_NAME                   SEGMENT_TYPE           GB

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

TBSG                           TB30                           IND_PAYDATEID                  INDEX      1.94335938

以下表压缩后空间释放了23GB：

压缩前：

OWNER                          TABLESPACE_NAME                SEGMENT_NAME                   SEGMENT_TYPE               GB

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

TBSG                           TB30                           TB_CNB_PAYRECORDINFO_2009_BAK    TABLE              28.1474609

压缩后：

OWNER                          TABLESPACE_NAME                SEGMENT_NAME                   SEGMENT_TYPE               GB

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

TBSG                           TESTMOVE                        TB_CNB_PAYRECORDINFO_2009_BAK  TABLE          4.5625

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25702/viewspace-711755/，如需转载，请注明出处，否则将追究法律责任。