朋友遇到一个奇怪的问题：
SQL> SELECT SUM(BYTES/1024/1024/1024) FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SAMENAME';
SUM(BYTES/1024/1024/1024)
-------------------------
170.380554
SQL> SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SAMENAME';
SUM(BYTES)/1024/1024/1024
-------------------------
262.46875
SQL> select sum(bytes)/1024/1024/1024 from dba_free_space where tablespace_name='SAMENAME';
SUM(BYTES)/1024/1024/1024
-------------------------
25.5291748
可以看到：
free_space + used_spaceselect sum(bytes)/(1024*1024) as mbytes from dba_segments where tablespace_name='';
MBYTES
---------
66711.0
SQL> select sum(bytes)/(1024*1024) as mbytes from dba_free_space where tablespace_name='';
MBYTES
---------
53772.2
SQL> select sum(bytes)/(1024*1024) as mbytes from dba_data_files where tablespace_name='';
MBYTES
---------
66000.0
里面明显地不吻合。
下面是METALINK的回应：
Please confirm that tablespace is LMT and then perform following action plan:
1) Rebuilt the bitmap.
SQL> exec dbms_space_admin.tablespace_rebuild_bitmaps('tablespace_name');
2) Run TABLESPACE_FIX_SEGMENT_EXTBLKS to re-compute the SEG$ total.
SQL> exec DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS('tablespace_name')
3) Now check if the columns are almost matching. If still facing the issue, then please upload the output of below queries :
但实际上我什么都没做，过两天就自动好了。出问题的是BLOB。
with a as (select sum(bytes) a_bytes from dba_data_files where tablespace_name='USERS'),
b as (select sum(bytes) b_bytes from dba_segments where tablespace_name='USERS'),
c as (select sum(bytes) c_bytes from dba_free_space where tablespace_name='USERS')
select (a.a_bytes-b.b_bytes-c.c_bytes)/1024 from a,b,c
－－－－－－－－－－－
６４
