oracle dba_segments 查询不出结果

来源：互联网发布：魔兽世界怀旧服知乎编辑：程序博客网时间：2024/04/24 07:00

前两天刚装了个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

之后再查询就非常快的出结果了。

0 0