系统内发现应用人员备份的两张历史表,通过压缩释放了近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
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/