Yes, the only problem is that doing a join with the dba_extents
query makes this run rather long.
Jared
"Thomas Day" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/24/2003 10:39 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: Slow database, too MANY buffers???
So this is what you're looking for?
col segment_name format a30
col segment_type format a10
select segment_name, segment_type, count(*)
from dba_extents, x$bh
where file_id = file# and dbablk between block_id and
block_id + blocks - 1
group by segment_name, segment_type
HAVING count(*) > 5
ORDER BY 3
/
Jared.Still
@radisys.com To: Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent by: root cc:
Subject: Re: Slow database,
too MANY buffers???
01/24/2003 12:34
PM
Please respond
to ORACLE-L
I just use this script that I originally used for finding
which object corrupt blocks are in.
Serves well for this as well.
Note that file_id is the incorrect column to
use on a database with > 1022 data files.
I think you need to use relative_fno in that case.
Jared
-- ora_1578.sql
-- use args from ORA-1578 errors to find
-- file and segment generating the error
col cfileid new_value ufileid noprint
col cblockid new_value ublockid noprint
prompt File ID:
set term off feed off
select '&1' cfileid from dual;
set feed on term on
prompt Block ID:
set term off feed off
select '&2' cblockid from dual;
set feed on term on
--define ufileid=8
--define ublockid=129601
select file_name "FILE WITH CORRUPT BLOCK"
from dba_data_files
where file_id = &ufileid
/
col segment_name format a30
col segment_type format a15
select segment_name, segment_type
from dba_extents
where file_id = &ufileid and &ublockid between block_id and
block_id + blocks - 1
/
undef 1 2
"Thomas Day" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/24/2003 07:54 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
cc:
Subject: Re: Slow database, too MANY buffers???
We'll I don't want to show my ignorance but I'll never learn if I don't
ask. How do you get from DBABLK to PK_MATERIAL_ORDER_POOL?
Jared.Still
@radisys.com To: Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent by: root cc:
Subject: Re: Slow database,
too MANY buffers???
01/23/2003 04:09
PM
Please respond
to ORACLE-L
Well, I'm close.
I just ran this on the DEV database for an app that is in the 'upgrade'
process.
FILE# DBABLK COUNT(*)
---------- ---------- ----------
10 38968 6
11 22753 6
11 40180 6
11 74893 6
16 104388 6
16 104511 66
6 rows selected.
Which resolves to index PK_MATERIAL_ORDER_POOL.
Looks like further investigation is in order.
Jared
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Thomas Day
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
