OEM query replacement

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Wed, 19 Feb 2003 08:03:52 -0800
Message-ID: <F001.00551616.20030219080352@fatcity.com>

Hey all,

Since we've added a datafile, our "Tablespace Full" event in OEM has been hammering the DB. Waits go high on Controlfile I/O when the following is running (no doubt from the v$datafile scan):

select sum(f.bytes)/:"SYS_B_0", floor(sum(f.bytes)/:"SYS_B_1") from sys.dba_free_space f, v$datafile d where f.file_id = d.file# and d.status in (:"SYS_B_2", :"SYS_B_3") and tablespace_name = :"SYS_B_4"
group by tablespace_name

Anyone have a direct replacement query for this that performs better? We're on 8.1.7.4, but I wouldn't want to limit the query to this DB version. 8i or above would be nice.

BTW, we're using CURSOR_SHARING=FORCE, thus the SYS binds...

TIA!
Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Feb 19 2003 - 10:03:52 CST