EM dbconsole显示数据文件使用量为负数？

有网友在itpub上反应11g中查看EM dbconsole显示数据文件的使用量为负数,如图：     实际上导致(EM ( EM Home page -> Administration Tab -> Storage -> Datafiles ))显示负的使用量的Bug在11g和10g中均存在，这里将这些bug列出来以供参考:   10g Enterprise Manager Shows Negative Utilisation For An Oracle Datafile.

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3
This problem can occur on any platform.
Symptoms

EM ( EM Home page -> Administration Tab -> Storage -> Datafiles ) shows negative 
utilization for some files. Value in Used (MB) column is negative.

Cause
This is because of Bug:5083393.

If the object is dropped and not purged, the value of FILE_ID / REL_FNO may be wrong in 
DBA_FREE_SPACE and this cause EM to display wrong value in Used (MB) column.

Solution
If available, download Patch:5083393 to resolve this issue.
Note that this patch is available on 10.2.0.2/10.2.0.3 for some platforms at the 
time of this writing this article (JULY 2007).

Workaround :
-------------
Purge all objects from the recycle bin.

$sqlplus "/as sysdba"
sql> purge dba_recyclebin 

Hdr: 5083393 10.1.0.4 RDBMS 10.1.0.4 SPACE PRODID-5 PORTID-912
Abstract: VALUE OF FILE_ID AND RELATIVE_FNO OF DBA_FREE_SPACE IS DIFFERENT

PROBLEM:
--------
When the table is dropped, the value of the FILE_ID column and
the RELATIVE_FNO column of dba_free_space might be different.

SQL> select * from dba_free_space where tablespace_name = 'TBS_TEST1';

TABLESPACE_NAME FILE_ID BLOCK_ID    BYTES BLOCKS RELATIVE_FNO
--------------- ------- -------- -------- ------ ------------
E
E
E
TBS_TEST1             6      105    65536      8            6
TBS_TEST1             6      113    65536      8            6
TBS_TEST1             6      121    65536      8            6
TBS_TEST1             6        9    65536      8            7   purge table tbs_test01;

Table purged.

SQL> select * from dba_free_space where tablespace_name = 'TBS_TEST1';

TABLESPACE_NAME FILE_ID BLOCK_ID    BYTES BLOCKS RELATIVE_FNO
--------------- ------- -------- -------- ------ ------------
TBS_TEST1             6        9   983040    120            6
TBS_TEST1             7        9   983040    120            7
TBS_TEST1             8        9 20905984   2552            8

In a word, there seems to be a problem in the display of an free space
concerning the segment that exists in RECYCLE BIN.

WORKAROUND:
-----------
None

RELATED BUGS:
-------------
None

REPRODUCIBILITY:
----------------
  100% (both In-house and Ct' site) 
  Rep?     Platform               RDBMS Ver. 
  -------  ---------------------  ---------- 
  Y(100%)  912 Intel Windows NT   10.1.0.4
  Y(100%)  912 Intel Windows NT   10.1.0.5
  Y(100%)  912 Intel Windows NT   10.2.0.2
  Y(100%)   46 Linux x86          10.1.0.4

  11gR2 EMCONSOLE SHOWS NEGATIVE VALUES WHEN VIEWING DATAFILE USAGE SPACE IN 11.2.0.1  

Hdr: 9868375 11.2.0.1 STORAGE 11.2.0.1 PRODID-1366 PORTID-226 9548105
Abstract: EMCONSOLE SHOWS NEGATIVE VALUES WHEN VIEWING DATAFILE USAGE SPACE IN 11.2.0.1

PROBLEM:
--------
The display of the usage of the data files (Tablespaces -> Datafiles) shows 
values in the range negative. The fill rate is not shown correctly.

This is the same exact issue as explained in note 559414.1 but on top of 
11.2.0.1 and also the issue does not seem fixed when trying the workaround by 
clearing the recycle bin or by turning it off.

DIAGNOSTIC ANALYSIS:
--------------------
Checked running the statements from sqlplus they provided the correct value 
and checked this Bug 5549540 seems it should have been fixed in 11G but still 
having the same issue.

WORKAROUND:
-----------
To run the statements and check the data file usage from sqlplus.

  11gR1 Grid Control Shows Duplicate Datafile Names Multiple times (Some With Negative Values) When Drilling Onto Tablespace Link

Applies to:
Enterprise Manager Grid Control - Version: 10.2.0.5 to 11.1.0.1 - Release: 10.2 to 11.1
Information in this document applies to any platform.
Symptoms

The list of datafiles displayed for a tablespace in EM Grid Control is wrong, so that some 
datafiles are listed repeatedly multiple times, and some of the sizes are wrong and showing a negative value.

This is seen from database home page => server tab => tablespaces.
Click on tablespace name, a list of datafiles associated with that tablespace should be returned.

Cause
If the SQL statement which Grid Control uses is run manually at Sqlplus, the same output of the 
same datafile listed multiple times can also be seen. 

Here you have the SQL statement launched by the Grid Control to collect the list of datafiles :

SELECT d.file_name, TO_CHAR((d.bytes / 1024 / 1024), '99999990.000'), 
NVL(TO_CHAR(((d.bytes - s.bytes) / 1024 / 1024), '99999990.000'), TO_CHAR((d.bytes / 1024 / 1024), '99999990.000')), 
d.file_id, d.autoextensible, d.increment_by, d.maxblocks FROM sys.dba_data_files d, 
(SELECT file_id, SUM(bytes) bytes FROM sys.dba_free_space WHERE tablespace_name = '&tablespaceName' 
GROUP BY file_id) s WHERE (s.file_id (+)= d.file_id) AND (d.tablespace_name = '&tablespaceName')

This is the issue described in following bug :

Bug 8499373 GRID CONTROL SHOWS SAME DATAFILE NAME REPEATEDLY WHEN DRILL INTO A TABLESPACE

This bug is actually closed as a duplicate of a database bug Bug 8570838 WRONG RESULTS FOR QUERY AGAINST 
DBA_DATA_FILES IN 10.2.0.4
The root cause of the database bug is :
Bug 7155655 Intermittent Wrong Results/Plan Instability from Join Predicate Push Down (JPPD)

Solution

Apply the Patch 7155655. 

Note :
Depending on platform it may be necessary to apply a merge patch for this - eg. in some cases it was 
necessary to apply Patch 7278117.

Hdr: 9748363 11.2.0.1 STORAGE 11.1.0.1 PRODID-1366 PORTID-226 9548105
Abstract: NEGATIVE VALUES REPORTED BY GRIDCONTROLFOR TABLESPACE WITH AT LEAST TWO DATAFILE

*** 05/25/10 09:46 am ***
TAR
---
3-1772640611

Problem Description
-------------------
The Used (MB) column on the datafiles summary webpage in the 11.2 grid 
control 
shows negative values for a tablespace containing at least two datafiles

Environment Information
-----------------------

Test Case Step-by-Step Instructions
-----------------------------------

Test Case Location
------------------

Diagnostic Analysis
-------------------
This is the same as bug 9548105 except for this is for GridControl vs 
dbconsole.
SELECT d.file_name, TO_CHAR((d.bytes / 1024 / 1024), '99999990.000'),
NVL(TO_CHAR(((d.bytes - s.bytes) / 1024 / 1024), '99999990.000'),
TO_CHAR((d.bytes / 1024/ 1024), '99999990.000')), d.file_id, 
d.autoextensible,
d.increment_by, d.maxblocks FROM sys.dba_data_files d,  
(SELECT ts.name tablespace_name, SUM(e.length * ts.blocksize) bytes  
FROM sys.fet$ e, sys.ts$ ts  WHERE ts.ts# = e.ts# GROUP BY ts.name  
UNION ALL  SELECT ts.name tablespace_name, SUM(e.blocks * ts.blocksize) bytes 

FROM sys.dba_lmt_free_space e, sys.ts$ ts  WHERE ts.ts# = e.tablespace_id 
GROUP BY ts.name) s
WHERE (s.tablespace_name = d.tablespace_name) AND (d.tablespace_name = :1)

Performance
-----------

NLS Information
---------------

-------

Log Files Location
------------------

Reproducibility
---------------
yes

URL
---

Did you test with the latest version?
-------------------------------------

Available Workarounds
---------------------
none

Related Bugs
------------
9548105