本帖最后由 wushi8 于 2012-12-29 15:24 编辑
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
这个表是一个空表:
SQL> select count(*) from DXT_TRANSACTION_ACCOUNTS_0707;
COUNT(*)
----------
0
查看表的信息,这个表占了1G多的空间:
SQL> select s.segment_name,s.segment_type,s.blocks,s.bytes/1024/1024 from dba_segments s where s.segment_name='DXT_TRANSACTION_ACCOUNTS_0707';
SEGMENT_NAME SEGMENT_TYPE BLOCKS S.BYTES/1024/1024
-------------------------------------------------------------------------------- ------------------ ---------- -----------------
DXT_TRANSACTION_ACCOUNTS_0707 TABLE 183856 1436.375
初步判断应该是之前有大量数据,后来delete了,选成高水位,用shrink清除高水位:
SQL> alter table DXT_TRANSACTION_ACCOUNTS_0707 enable row movement;
Table altered
SQL> alter table DXT_TRANSACTION_ACCOUNTS_0707 shrink space;
Table altered
重新收集统计信息:
SQL> exec dbms_stats.gather_table_stats(ownname=>'apps',tabname=>'DXT_TRANSACTION_ACCOUNTS_0707');
PL/SQL procedure successfully completed
再次查看,占用空间尽然没有减小,为什么呢??
SQL> select s.segment_name,s.segment_type,s.blocks,s.bytes/1024/1024 from dba_segments s where s.segment_name='DXT_TRANSACTION_ACCOUNTS_0707';
SEGMENT_NAME SEGMENT_TYPE BLOCKS S.BYTES/1024/1024
-------------------------------------------------------------------------------- ------------------ ---------- -----------------
DXT_TRANSACTION_ACCOUNTS_0707 TABLE 183856 1436.375
表创建原始信息:
-- Create table
create table DXT_TRANSACTION_ACCOUNTS_0707
(
transaction_id NUMBER,
reference_account NUMBER not null,
last_update_date DATE not null,
last_updated_by NUMBER not null,
......................
gl_sl_link_id NUMBER,
ussgl_transaction_code VARCHAR2(30)
)
tablespace APPS_TS_TX_DATA
pctfree 10
initrans 10
maxtrans 255
storage
(
initial 128
next 128
minextents 1
maxextents unlimited
pctincrease 0
);
原来是Oracle BUG 463101.1
执行下面这条语句可以查看到所有信息不正确的表:
select /*+ RULE */ s.tablespace_name, s.segment_name segment, s.partition_name,
s.owner owner, s.segment_type, s.blocks sblocks, e.blocks eblocks,
s.extents sextents, e.extents eextents, s.bytes sbytes, e.bytes ebytes
from dba_segments s,
(select count(*) extents, sum(blocks) blocks, sum(bytes) bytes, segment_name,
partition_name, segment_type, owner
from dba_extents
group by segment_name,partition_name,segment_type,owner) e
where s.segment_name=e.segment_name
and s.owner = e.owner
and (s.partition_name = e.partition_name or s.partition_name is null)
and s.segment_type = e.segment_type
and s.owner not like 'SYS%'
and ((s.blocks <> e.blocks) or (s.extents <> e.extents) or (s.bytes <> e.bytes));
通过下面这个过程修正这些问题:
DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS('');
我这个表空间有400多G,执行时间大概5分钟,还是不错的。
这样再查询dba_segments视图就正常了。