Re: how to know ,the tables which are accessed via FTS?

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-----
<hemantchitale_at_charteredsemi.com>
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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: 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).