Inactive and locked sessions and Oracle Applications 11i end users identified

What SQL can I use to get the following output?

       SID    SERIAL# ACTION                           STATUS

---------- ---------- -------------------------------- --------

       216       2147 FRM:MBLESSING:CHB HR Center      INACTIVE

Also, I'd like to know the SQL that would output end users user names for those that have locked up processes or forms or have invalid locks.
janthonynAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sonicefuCommented:

SELECT b.username "Oracle User", a.spid "Unix PID", b.SID "Oracle Sid",
       b.serial# "Oracle Serial", b.osuser "OS User", b.machine "Computer",
       b.program "Program",
       TO_CHAR (logon_time, 'HH24:MI:SS,yyyy/mm/dd') "Login Time",
       b.action "Session Action", c.action "SQL Action", lockwait "LockWait",
       status "Status", optimizer_cost "Optimizer_cost",
       c.sql_text "SQL Text"
  FROM v$process a, v$session b, v$sql c
 WHERE
       --spid=&PID and
       a.addr = b.paddr
   AND b.sql_address = c.address
   AND b.sql_hash_value = c.hash_value;

Open in new window

0
sonicefuCommented:
or use the following SQL to get all information that you need
SELECT b.username "Oracle User", b.SID "Oracle Sid",
       b.serial# "Oracle Serial", b.osuser "OS User", b.machine "Computer",
       b.program "Program",
       TO_CHAR (logon_time, 'HH24:MI:SS,yyyy/mm/dd') "Login Time",
       b.action "Session Action", c.action "SQL Action", lockwait "LockWait",
       status "Status", optimizer_cost "Optimizer_cost",
       c.sql_text "SQL Text"
  FROM v$session b, v$sql c
 WHERE b.sql_address = c.address AND b.sql_hash_value = c.hash_value;

Open in new window

0
joselfmCommented:
Hi,
I think this would help you:


SELECT BS.USERNAME "Blocking User",
 
BS.USERNAME "DB User",
 
WS.USERNAME "Waiting User",
 
BS.SID "SID",
 
WS.SID "WSID",
 
BS.SERIAL# "Serial#",
 
BS.SQL_ADDRESS "address",
 
BS.SQL_HASH_VALUE "Sql hash",
 
BS.PROGRAM "Blocking App",
 
WS.PROGRAM "Waiting App",
 
BS.MACHINE "Blocking Machine",
 
WS.MACHINE "Waiting Machine",
 
BS.OSUSER "Blocking OS User",
 
WS.OSUSER "Waiting OS User",
 
BS.SERIAL# "Serial#",
 
WS.SERIAL# "WSerial#",
 
DECODE(WK.TYPE,'MR','Media Recovery',
 
'RT','Redo Thread',
 
'UN','USER Name',
 
'TX','Transaction',
 
'TM','DML',
 
'UL','PL/SQL USER LOCK',
 
'DX','Distributed Xaction',
 
'CF','Control FILE',
 
'IS','Instance State',
 
'FS','FILE SET',
 
'IR','Instance Recovery',
 
'ST','Disk SPACE Transaction',
 
'TS','Temp Segment',
 
'IV','Library Cache Invalidation',
 
'LS','LOG START OR Switch',
 
'RW','ROW Wait',
 
'SQ','Sequence Number',
 
'TE','Extend TABLE',
 
'TT','Temp TABLE',
 
WK.TYPE) LOCK_TYPE,
 
DECODE(HK.LMODE,0,'None',
 
1,'NULL',
 
2,'ROW-S (SS)',
 
3,'ROW-X (SX)',
 
4,'SHARE',
 
5,'S/ROW-X (SSX)',
 
6,'EXCLUSIVE',
 
TO_CHAR(HK.LMODE)) MODE_HELD,
 
DECODE(WK.REQUEST,0,'None',
 
1,'NULL',
 
2,'ROW-S (SS)',
 
3,'ROW-X (SX)',
 
4,'SHARE',
 
5,'S/ROW-X (SSX)',
 
6,'EXCLUSIVE',
 
TO_CHAR(WK.REQUEST)) MODE_REQUESTED,
 
TO_CHAR(HK.ID1) LOCK_ID1,
 
TO_CHAR(HK.ID2) LOCK_ID2,
 
DECODE(HK.BLOCK,0,'NOT Blocking', /* Not blocking any other processes */
 
1,'Blocking', /* This lock blocks other processes */
 
2,'Global', /* This lock is global, so we can't tell */
 
TO_CHAR(HK.BLOCK)) BLOCKING_OTHERS
 
FROM V$LOCK HK,
 
V$SESSION BS,
 
V$LOCK WK,
 
V$SESSION WS
 
WHERE HK.BLOCK = 1
 
AND HK.LMODE != 0
 
AND HK.LMODE != 1
 
AND WK.REQUEST != 0
 
AND WK.TYPE (+) = HK.TYPE
 
AND WK.ID1 (+) = HK.ID1
 
AND WK.ID2 (+) = HK.ID2
 
AND HK.SID = BS.SID (+)
 
AND WK.SID = WS.SID (+)
 
AND (BS.USERNAME IS NOT NULL)
 
AND (BS.USERNAME <> 'SYSTEM')
 
AND (BS.USERNAME <> 'SYS')
 
ORDER BY 1;

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

janthonynAuthor Commented:

*** SCRIPT START :  Session:APPSRO@caop(1)   5-Feb-2008 21:08:13 ***
Processing ...
  , v$sql c
     *
ORA-00942: table or view does not exist
*** Script stopped due to error ***
*** SCRIPT END :  Session:APPSRO@caop(1)   5-Feb-2008 21:08:13 ***

Can't test this if the table doesn't exist
0
janthonynAuthor Commented:
sonicefu,
The SQL did process in apps. I'll need to test tomorrow when more end users are logged in. I need to have a query that reports what end users are signed on, what sessions are inactive or locked, what forms are locked.

joselfm: I will have to retest tomorrow when more end users are signed in
0
schwertnerCommented:
To get rid of dead sessions set in SQLNET.ORA the parameter
SQLNET.EXPIRE_TIME = 10

Every 10 minutes the dead sessions will be dropped.

To see some info:

select count(*) from v$session
where status in ('ACTIVE','SNIPED','INACTIVE');

select distinct status from v$session;

select count(*), status from v$session
group by status;

  COUNT(*) STATUS
---------- ------------
        11 ACTIVE
       167 INACTIVE
       111 KILLED
        16 SNIPED

To figure out what you want as SYS run this:

--
--Monitor Session Idle Time
--
set pagesize 500
set linesize 150
set heading on
set underline '-'
col sid      format 999
col username format a10 truncated
col status   format a1  truncated
col logon    format a17
col idle     format a9
col program  format a30 truncated
col machine  format a10 truncated


select
   sid,username,status,machine,
   to_char(logon_time,'dd-mm-yy hh24:mi:ss') "LOGON",
   floor(last_call_et/3600)||':'||
   floor(mod(last_call_et,3600)/60)||':'||
   mod(mod(last_call_et,3600),60) "IDLE",
   program
from
   v_$session
where
   type='USER';
/


 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
janthonynAuthor Commented:
All these SQL's give me useful information, but I need to know end user names that go with that info. The username field tells me the database user and osuser tells me the operating system user. I need the end user name as it appears in the fnd_user table. Is there a join to that table that I can use for this? Secondly, idle time is an interesting metric. I control this with the system profile setting in the System Administrator application. What I'm focusing in on here is the sessions that are running after a network connection is lost or a form freezes and the end user exits the application and logs back in, starting a new session but leaving the previous session active and using resources. How can I query those types of inactive sessions? End user names associated with those sessions would be a big plus because then I could see patterns that identify the need for training or better equipment or a better fast formula, etc.
0
schwertnerCommented:
fnd_user doesnt exist in the SYS schema.
What you mean?


----> What I'm focusing in on here is the sessions that are running after a network connection is lost or a form freezes and the end user exits the application and logs back in <------

Exactly this task is done by:

To get rid of dead sessions set in SQLNET.ORA the parameter
SQLNET.EXPIRE_TIME = 10

Every 10 minutes the dead sessions will be dropped.
0
sonicefuCommented:
i think you can get this information using following tables
APPLSYS.FND_LOGINS 
APPLSYS.FND_LOGIN_RESPONSIBILITIES 
APPLSYS.FND_LOGIN_RESP_FORMS 
FND_CONCURRENT_REQUESTS 
APPLSYS.FND_UNSUCCESSFUL_LOGINS 
ICX.ICX_FAILURES

Open in new window

0
janthonynAuthor Commented:
If we make this setting: SQLNET.EXPIRE_TIME = 10
Will that automatically kill dead sessions, i.e. sessions running after a network outage or after a form crash and the end user xs out of the session? Or does this kill all sessions after 10 min idle time, i.e. an end user steps away from her desk for 11 mins?
0
sonicefuCommented:
I think this query will help you
SELECT c.owner, c.object_name, c.object_type,
       fu.user_name locking_fnd_user_name,
       fl.start_time locking_fnd_user_login_time, vs.module, vs.machine,
       vs.osuser, vlocked.oracle_username, vs.SID, vp.pid,
       vp.spid AS os_process, vs.serial#, vs.status, vs.saddr, vs.audsid,
       vs.process
  FROM fnd_logins fl,
       fnd_user fu,
       v$locked_object vlocked,
       v$process vp,
       v$session vs,
       dba_objects c
 WHERE vs.SID = vlocked.session_id
   AND vlocked.object_id = c.object_id
   AND vs.paddr = vp.addr
   AND vp.spid = fl.process_spid(+)
   AND vp.pid = fl.pid(+)
   AND fl.user_id = fu.user_id(+)
   AND c.object_name LIKE '%' || UPPER ('&tab_name_leaveblank4all') || '%'
   AND NVL (vs.status, 'XX') != 'KILLED'; --<-change it 

Open in new window

0
schwertnerCommented:
The setting: SQLNET.EXPIRE_TIME = 10
forces the Listener every 10 minutes to send probes to
all client applications that uses Oracle sessions and to check if they
are still running (alive). If the probe doesn't return the applications are shut down (dead)
and the listener will kill all appropriate sessions.

This has nothing to do with the IDLE time of the applications.
In Oracle Application server you can set separately the IDLE time of the
Oracle Forms - there was a SGI parameter or something similar.
0
janthonynAuthor Commented:
I haven't found what I need here, but have received helpful suggestions. So I will continue looking for the full solution, but close this question with awarded points
0
janthonynAuthor Commented:
Great suggestions, but did not end up with the tool that I need. I'll keep looking
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.