Doc

Name: OBJ_ACCESS.sql

Author: Mahnho Han

The number of sessions that is using a object

#

SET PAGESIZE 80

SET LINESIZE 130

CLEAR COLUMN

COLUMN OBJNAME FORMAT A40 HEADING "OBJECT NAME"

COLUMN ACNUM FORMAT 999 HEADING "No of Users"

SELECT A.OWNER||'.'||OBJECT OBJNAME, COUNT(SID) ACNUM,

INI_TRANS,MAX_TRANS,FREELISTS,PCT_FREE

FROM V$ACCESS A, DBA_TABLES T

WHERE OB_TYP = 2 /* table */

AND A.OWNER = T.OWNER

AND A.OBJECT = T.TABLE_NAME

GROUP BY A.OWNER,OBJECT,INI_TRANS,MAX_TRANS,FREELISTS,PCT_FREE

HAVING COUNT(SID) > 1

ORDER BY COUNT(SID) DESC

/