rem Name:    access.sql
rem Purpose: Show objects currently in use in the database
rem Usage:   @access <%owner.name%> <%type%> <%username%>
rem Subject: tuning
rem Attrib:  sql dba
rem Descr:
rem Notes:
rem SeeAlso: @locks

@setup1
define typ="upper('&&2')"
define usr="upper('&&3')"

column uname format a20 heading "USERNAME"
column oname format a30 heading "OBJECT_NAME"
 

select
s.username||','||s.sid uname
,a.owner||'.'||a.object oname
,a.type
from v$access a, v$session s
where a.sid = s.sid
and s.username like &&usr
and a.owner like &&o1
and a.object like &&n1
and a.type like &&typ
order by s.username, a.owner, a.object, a.type;

undef usr typ

@setdefs

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips