--
--  Name: objects_access_nbrusers.sql
--
--  Description: Objects - Display objects being accessed with number of users for each.  For multiple objects specify %.
--
--  Compatability: tested on 9i
--
--  Usage: @objects_access_users %
--         @objects_access_users objectname
--

set serveroutput on
set pagesize 1000
set linesize 133
set verify off

col object   for A34
col owner    for A16  trunc
col type     for A12  head  Obj|type
col accessed for 9999 head "Accessed|by # users"

select A.owner,A.object,A.TYPE,count(*) accessed
from   v$ACCESS  A
where  A.owner not in ('SYS','SYSTEM')
--and    A.TYPE = 'TABLE'
and object like '&&1'
group by A.owner,A.object,A.TYPE
order by accessed
/
--------------------------------------------------------------------------------
rem  Name: XObj1.sql 
rem  Date: 20-dec-2000
rem  Desc: Shows users accessing a specific table.

col SID      for 999
col owner    for A10 trunc
col type     for A6  trunc
col object   for A24 trunc
col command  for 999
col username for A14
set linesize 90

select  S.SID
       ,S.username
       ,S.OSuser
       ,A.owner
       ,A.object
       ,A.TYPE 
       ,S.command
from   v$ACCESS  A
      ,v$SESSION S
      ,AUDIT_ACTIONS C
where S.username is not null
and   S.SID    = A.SID
and   A.owner not in ('SYS','SYSTEM')
and   A.TYPE   = 'TABLE'
and   C.action = S.command
and   A.object like upper('&Object_name%')
/