ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle表及索引压缩

oracle表及索引压缩

原创 Linux操作系统 作者:nmgzw 时间:2019-05-09 22:39:05 0 删除 编辑
系统内发现应用人员备份的两张历史表,通过压缩释放了近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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2002-10-18

  • 博文量
    71
  • 访问量
    65445