--
--  Name: objects_access.sql
--
--  Description: Objects - Display objects being accessed for a schema.  For multiple schema specify %.
--
--  Compatability: tested on 9i
--
--  Usage: @objects_access %
--

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

column owner_access format a15
column object_access format a15
column username format a15
column osuser format a15

select a.owner owner_access,
       substr(a.object,1,30) object_access,
       a.type type_access,
       a.sid,
       b.username,
       b.osuser,
       b.program
from   v$access a,
       v$session b
where  a.sid   = b.sid
and    a.owner like upper('&1')
order by 1;

prompt
set pagesize 18