Oracle8i 8.1.7.2 on Tru64.
I've been using the query :
SELECT o.owner oowner, o.object_name oname
FROM dba_objects o,x$bh x
WHERE x.obj=o.data_object_id
AND o.object_type='TABLE'
AND standard.bitand(x.flag,524288)>0
AND o.owner<>'SYS'
group by owner, object_name
order by owner, object_name
/
This ends in "ORA-03113: end-of-file on communication channel" 3 out of 4
times
(but 1 out of 4 times I DO get the list of tables).
If I remember correctly it used to work in 8.0.5, except that I was joining
x.obj against o.object_id.
However, on another 8.1.7.0 on Solaris 8 database I consistently
get the list of Tables. The difference is that this database is less
busy. The Tru64 database is my Oracle Applications Database.
Any ideas why I get the ORA-3113 on my Oracle Applications Tru64 database ?
[other than that this is a really busy database].
Hemant K Chitale
Principal DBA
Chartered Semiconductor Manufacturing Ltd
"Alex Feinstein" <[EMAIL PROTECTED]> 12/03/2002 01:58 PM
Sent by: [EMAIL PROTECTED]
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group)
Subject: Re: how to know ,the tables which are accessed via FTS?
Gopal, Ganesh, ...
Small correction:
1. v$session_event does not have file#, block# and blocks. They are in
v$session_wait, which may be hard to catch.
2. Absolute best methode. But field name is "flag". not "class", you can
use bitand(flag,power(2,19) > 0 as a condition. When join to
dba_objects(user_objects) one should use field data_object_id, not
object_id. Take care about clusters, as all object in cluster share the
same data_object_id. Anothe (but slower) way is join with
dba_extents(user_extents).
4. This give accurate info to the file level, not segment level.
Alex.
----- Original Message -----
From: K Gopalakrishnan
To: Multiple recipients of list ORACLE-L
Sent: Monday, March 11, 2002 11:07 AM
Subject: RE: how to know ,the tables which are accessed via FTS?
Ganesh,
You can find FTS in the following methods:
1. THe existence of the 'db file scattered read' in the V$session_event.
From this we can find the file#, Block# and #of Blocks.
Using this info we can get the segment name from the dictionary.
2. ANy block read using Sequential Scan (Full Table Scan) will be flagged
as 0x80000 in the X$BH.CLASS. So existence of
i0x80000 in X$.BH tells there is an FTS in the buffer cache. So from X$BH
we can get the object name from the user_object
by joining X$.BH.OBJ
3. THen the normal SQL Trace with TKPROF
4. If the KCFIOPBR>KCFIOPYRin the X$KCFIO (exposed as V$FILESTAT as
PHYSICAL READS and PHYSICAL BLOCK READS)
then some of the segments in that datafile is read by FTS. (THis may not
give 100% accurate info)
5.. Any other thoughts?
Best Regards,
K Gopalakrishnan
Bangalore, INDIA
-----Original Message-----
From: Ganesh Raja [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 11, 2002 1:12 AM
To: LazyDBA.com Discussion
Subject: RE: how to know ,the tables which are accessed via FTS?
What will that Acheive... this will not give u Tables that have FTS
Done on them.
Best Regards,
Ganesh R
Tel : +971 (4) 397 3337 Ext 420
Fax : +971 (4) 397 6262
HP : +971 (50) 7456019
-----Original Message-----
From: vipin jain [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 11, 2002 1:02 PM
To: LazyDBA.com Discussion
Subject: Re: how to know ,the tables which are accessed via FTS?
USE analyze table with compute statistcs....
Thanks and regards,
Vipin Jain
----- Original Message -----
From: Atul Gupta
To: LazyDBA.com Discussion
Sent: Monday, March 11, 2002 2:20 PM
Subject: how to know ,the tables which are accessed via FTS?
Hi Gurus ,
One little question
how will i get to know what all tables are accessed via full
table scan ?
any help will be highly appreciated
Thnax in advance
Atul Gupta
[This e-mail is confidential and may also be privileged. If you are not the
intended recipient, please delete it and notify us immediately; you should
not copy or use it for any purpose, nor disclose its contents to any other
person. Thank you.]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
