script monitoring

select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''''
from v$session
where sid in (
select SID from v$access
where object like '%NAMA_OBJECTS%')

SELECT objects.owner,
       objects.object_name,
       objects.object_type,
       user1.user_name         locking_fnd_user_name,
       login.start_time        locking_fnd_user_login_time,
       vs.module,
       vs.machine,
       vs.osuser,
       vlocked.oracle_username,
       vs.sid,
       vp.pid,
       vp.spid                 os_process,
       vs.serial#,
       vs.status,
       vs.saddr,
       vs.audsid,
       vs.process
  FROM fnd_logins      login,
       fnd_user        user1,
       v$locked_object vlocked,
       v$process       vp,
       v$session       vs,
       dba_objects     objects
 WHERE vs.sid = vlocked.session_id
   AND vlocked.object_id = objects.object_id
   AND vs.paddr = vp.addr
   AND vp.spid = login.process_spid(+)
   AND vp.pid = login.pid(+)
   AND login.user_id = user1.user_id(+)

SELECT p.username pu,
        s.username su,
        s.status stat,
        s.sid ssid,
        s.serial# sser,
        lpad(p.spid,7) spid,
        substr(sa.sql_text,1,540) txt
   FROM v$process p, v$session s, v$sqlarea sa
  WHERE p.addr=s.paddr
    AND s.username='APPS'
    AND s.status='INACTIVE'
    AND s.sql_address=sa.address(+)
    AND s.sql_hash_value=sa.hash_value(+)
 ORDER BY 1,2,7


/* Formatted on 8/15/2012 6:31:35 PM (QP5 v5.185.11230.41888) */
  SELECT NVL (S.USERNAME, 'Internal') username,
         NVL (S.TERMINAL, 'None') terminal,
         L.SID || ',' || S.SERIAL# Kill,
         U1.NAME || '.' || SUBSTR (T1.NAME, 1, 20) tab,
         DECODE (L.LMODE,
                 1, 'No Lock',
                 2, 'Row Share',
                 3, 'Row Exclusive',
                 4, 'Share',
                 5, 'Share Row Exclusive',
                 6, 'Exclusive',
                 NULL)
            lmode,
         DECODE (L.REQUEST,
                 1, 'No Lock',
                 2, 'Row Share',
                 3, 'Row Exclusive',
                 4, 'Share',
                 5, 'Share Row Exclusive',
                 6, 'Exclusive',
                 NULL)
            request,
         s.Logon_time
    FROM v$LOCK L,
         v$SESSION S,
         SYS.USER$ U1,
         SYS.OBJ$ T1
   WHERE     L.SID = S.SID
         AND T1.OBJ# = DECODE (L.ID2, 0, L.ID1, L.ID2)
         AND U1.USER# = T1.OWNER#
         AND S.TYPE != 'BACKGROUND'
ORDER BY 1, 2, 5;

select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''''
from v$session
where sid in (
select SID from v$access
where object like '%PER_ALL_ASSIGNMENTS_F%')



/* Formatted on 8/15/2012 8:23:02 PM (QP5 v5.185.11230.41888) */
  SELECT oracle_username || ' (' || s.osuser || ')' username,
         s.sid || ',' || s.serial# sess_id,
         owner || '.' || object_name object,
         object_type,
         DECODE (l.block,  0, 'Not Blocking',  1, 'Blocking',  2, 'Global')
            status,
         DECODE (v.locked_mode,
                 0, 'None',
                 1, 'Null',
                 2, 'Row-S (SS)',
                 3, 'Row-X (SX)',
                 4, 'Share',
                 5, 'S/Row-X (SSX)',
                 6, 'Exclusive',
                 TO_CHAR (lmode))
            mode_held
    FROM v$locked_object v,
         dba_objects d,
         v$lock l,
         v$session s
   WHERE     v.object_id = d.object_id
         AND v.object_id = l.id1
         AND v.session_id = s.sid
ORDER BY oracle_username, session_id


SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
WHERE status ='INACTIVE';