有网友在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
