ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引空间三倍于表大小

索引空间三倍于表大小

原创 Linux操作系统 作者:china_yebin 时间:2011-02-15 11:13:26 0 删除 编辑
 
INDEX_HISTORY表空间空间占用很大,检查里面的segment使用情况
select owner, segment_name, partition_name, segment_type, (bytes)/1024/1024/1024 GB
from dba_segments where tablespace_name = 'INDEX_HISTORY' order by GB desc;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE GB
MISC IND_SUBSCRIBER_LASTCHADATE  INDEX 99.3359375
MISC IDX_SUBSMODE_ID  INDEX 13.625
MISC PK_SUBSOTHERINFO_MID  INDEX 12.4375
发现有三个索引占用较多的空间
select owner, segment_name, partition_name, segment_type, (bytes)/1024/1024/1024 GB
from dba_segments
where (owner, segment_name) in (select owner, table_name from dba_indexes where wner = 'MISC' and index_name = 'IND_SUBSCRIBER_LASTCHADATE')
 order by GB desc ;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE GB
MISC SUBSCRIBER SUBSCRIBER_P1 TABLE PARTITION 8.4033203125
MISC SUBSCRIBER SUBSCRIBER_P2 TABLE PARTITION 8.4033203125
MISC SUBSCRIBER SUBSCRIBER_P3 TABLE PARTITION 8.4013671875
MISC SUBSCRIBER SUBSCRIBER_P4 TABLE PARTITION 8.3974609375
select owner, segment_name, partition_name, segment_type, (bytes)/1024/1024/1024 GB
from dba_segments
where (owner, segment_name) in (select owner, table_name from dba_indexes where wner = 'MISC' and index_name = 'IDX_SUBSMODE_ID')
 order by GB desc ;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE GB
MISC SUBSMODE  TABLE 21.58984375
select owner, segment_name, partition_name, segment_type, (bytes)/1024/1024/1024 GB
from dba_segments
where (owner, segment_name) in (select owner, table_name from dba_indexes where wner = 'MISC' and index_name = 'PK_SUBSOTHERINFO_MID')
 order by GB desc ;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE GB
MISC SUBSOTHERINFO  TABLE 9.6630859375
 
索引空间三倍于表大小,这东西可不是一般生产系统能见到的。
运行6年的系统,加上缺乏有效的维护才可以形成这一奇特现象,纯天然啊。
 

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

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

注册时间:2009-06-11

  • 博文量
    32
  • 访问量
    51985