Re: ** find whether table or index being accessed

From: Yong Huang <yong321_at_yahoo.com>
Date: Wed, 19 Nov 2003 07:10:00 -0800
Message-ID: <F001.005D729F.20031119071000@fatcity.com>

Tanel,

Raj must be talking about userenv('SCHEMAID'). Change that to another user's user_id as seen in dba_users, you should see that user's object usage:

SQL> select * from v$object_usage;

no rows selected

SQL> select io.name, t.name,

  2         decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
  3         decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
  4         ou.start_monitoring,
  5         ou.end_monitoring

  6 from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou   7 where io.owner# = 32
  8 and i.obj# = ou.obj#
  9 and io.obj# = ou.obj#
 10 and t.obj# = i.bo#;

NAME                           NAME                           DEC DEC

START_MONITORING END_MONITORING

------------------------------ ------------------------------ --- ---
------------------- -------------------
AGENT_LICENSE_STATEPRDNO_IND   AGENT_LICENSE                  YES YES

11/18/2003 10:06:27
...

Yong Huang

• Tanel Poder <tanel.poder.003_at_mail.ee> wrote:
  > Well, it's code is:
  >
  > select io.name, t.name,
  > decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
  > decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
  > ou.start_monitoring,
  > ou.end_monitoring
  > from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
  > where io.owner# = userenv('SCHEMAID')
  > and i.obj# = ou.obj#
  > and io.obj# = ou.obj#
  > and t.obj# = i.bo#
  >
  > All of the referenced objects are tables under sys.
  >
  > Maybe you were talking about v$session_object_cache?
  >
  > Tanel.
  >
  > ----- Original Message -----
  > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
  > Sent: Wednesday, November 19, 2003 3:20 PM
  >
  >
  > > MG,
  > >
  > > AFAIK v$object_usage is ONLY for current user ... you have to hack it to
  > see remaining data.
  > >
  > > Raj
  

  ---

Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yong Huang
  INET: yong321_at_yahoo.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 Nov 19 2003 - 09:10:00 CST