Whilst your query really ought not to crash,
bear in mind that X$ objects are not subject
to any form of normal read consistency, so
you may be hitting some strange side-effect
of in-flux blocks.
You may do better to reduce the run-time
of the query by doing the select and group
by on x$bh only as an inline view, and then
joining to dba_objects - and since you are joining
to x$bh why not join to obj$ ?
Something like:
select
from
(
select obj
from x$bh
where to_number(bitand(flag, power(2,19))) = 1
group by obj
) bh,
obj$ ob
where
ob.dataobj# = bh.obj
;
By restricting the object_type to "TABLE"
you will be missing index fast full scans,
of course. And won't you also miss
scans on partitioned objects and
clustered objects.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
-----Original Message-----
<[EMAIL PROTECTED]>
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 12 March 2002 07:46
|
|
|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
|
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
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).
