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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25702/viewspace-711755/