前两天刚装了个11.2.0.4 AIX环境,建了一堆表空间,导入了2T数据。
结果执行下面命令的时候,死活不出结果。
select segment_type,sum(bytes)/1024/1024/1024 from dba_segments where owner ='USER1' group by segment_type;
cancel的时候报错
异常退出DBMS_SPACE_ADMIN
非常奇怪,为什么一个查询操作会用到这个包呢?
百度上搜了一下,发现eagle大师的文章提到了一点
http://www.dbafan.com/blog/?p=146
解决方法是运行procedure: TABLESPACE_FIX_SEGMENT_EXTBLKS
DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS(‘tablespace_name’);
Issuing DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS fixes the DBA_SEGMENTS values. The tablespace
must be kept online and read/write when this procedure is called. Runing this procedure requires COMPATIBLE parameter to be set to 10.0.0.0 or greater.
The procedure fixes extents, blocks and bytes in the segment headers to synchronize seg$ and
segment header entries.
It holds the allocation enqueue for the tablespace till the command is completed and this may delay some sort of operations in this tablespace (new extent allocation, deallocate extent, etc.). So it needs to be run during an idle period.
我猜想可能也是我这边的表空间有问题,于是对所有的业务表空间执行了一下DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS
之后再查询就非常快的出结果了。