日常监控免不了监控表空间使用情况，经常查询的视图是dba_free_space 和dba_data_files, 类似以下语句：
set pagesize 9999
set pagesize 9999
set linesize 132
select
f.tablespace_name,
a.Total_mb,
f.Free_mb,
round(a.total_MB-f.free_mb,2) Used_mb,
round((f.free_MB/a.total_MB)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total_MB from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free_MB from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free"
/

但是有时发现查询他非常缓慢，这是因为dba_free_space 里面条目过多，所以查询起来非常缓慢，为啥这个view里面条目如此
之多呢，有时候会发现里面的extent居然还是连续的，其实这是因为drop 对象造成的，下面的例子可以展示一下这个场景：
create table t1 as select * from dba_tables where rownum<1000;
create table t2 as select * from dba_tables where rownum<1000;
分别新建两个表，然后查询他们的extent情况
select * from dba_extents where segment_NAME IN ('T1','T2')

MAOB T1 TABLE USERS 0 4 520 65536 8 4
MAOB T1 TABLE USERS 1 4 528 65536 8 4
MAOB T1 TABLE USERS 2 4 536 65536 8 4
MAOB T1 TABLE USERS 3 4 7184 65536 8 4
MAOB T1 TABLE USERS 4 4 7192 65536 8 4
MAOB T1 TABLE USERS 5 4 7200 65536 8 4

MAOB T2 TABLE USERS 0 4 7208 65536 8 4   <<T2 是接着T1的最后一个extent分配的
MAOB T2 TABLE USERS 1 4 7216 65536 8 4
MAOB T2 TABLE USERS 2 4 7224 65536 8 4
MAOB T2 TABLE USERS 3 4 7232 65536 8 4
MAOB T2 TABLE USERS 4 4 7240 65536 8 4
MAOB T2 TABLE USERS 5 4 7248 65536 8 4

对两个表进行drop 之后，我们查询 dba_free_space情况
select * from dba_free_space where file_id=4 order by block_id

USERS 4 520 65536 8 4
USERS 4 528 65536 8 4
USERS 4 536 65536 8 4 <<<以上3个是T1的前三个extent 都是连续的，并没有进行合并
USERS 4 640 41943040 5120 4
USERS 4 7184 65536 8 4
USERS 4 7192 65536 8 4
USERS 4 7200 65536 8 4 <<<以上3个是T1的后三个extent 都是连续的，并没有进行合并

USERS 4 7208 65536 8 4
USERS 4 7216 65536 8 4
USERS 4 7224 65536 8 4
USERS 4 7232 65536 8 4
USERS 4 7240 65536 8 4
USERS 4 7248 65536 8 4      <<以上6个是T2的extent，都是连续的，并没有进行合并
USERS 4 7256 524288 64 4   <<这个和T2的extents是连续的，但是也没有合并

这些extents都是保持原样的出现在了dba_free_space，所以造成条目增多，我们接下来进行purge
purge recyclebin;
我们再次查询 dba_free_space情况 select * from dba_free_space where file_id=4 order by block_id

USERS 4 520 196608 24 4           <<3个extent 已经合并成一个24blocks的新extent
USERS 4 640 41943040 5120 4
USERS 4 7184 1114112 136 4    <<6个extent 共计72个block连同原有的相邻extent 里面的64 个block合并成为了一个新的136block的extent
dba_free_space条目也大幅减少，所以对于recyclebin里面有着大量drop的表，尤其是这些表曾经分配了大量的extent，就会出现此类问题。

ITPub博客

由于回收站存在大量对象导致查询表空间使用率较慢

最新文章