SQL statement to show the holder and waiter session information

SELECT    s.SID SID, s.username uname,
          DECODE (l.TYPE, 'TX', 'TX', 'UL', 'USR', 'SYS') ltype,
          DECODE (l.lmode,
                  1, 'Null',
                  2, 'Row-S',
                  3, 'Row-X',
                  4, 'Share',
                  5, 'S/Row-X',
                  6, 'Exclusive'
                 ) lmode,
           DECODE (l.request, 0, 'No', 'Yes') blocked,
           DECODE (l.request,
                  0, NULL,
                  'Waiting on session '
                  || TO_CHAR (b.SID)
                  || ' (' || s3.program || ')'
                 ) details,
           s.paddr, s2.sql_text, (b.ctime / 60) waiting
    FROM v$session s, v$lock l, v$lock b, v$sql s2, v$session s3, v$process p
   WHERE s.username = 'APPS'
     AND s.SID = l.SID
     AND l.TYPE != 'TM'
     AND l.id1 = b.id1(+)
     AND b.request(+) = 0
     AND l.request = 6
     AND b.SID = s3.SID(+)
     AND p.addr(+) = s3.paddr
     AND s2.hash_value = s.sql_hash_value(+)
ORDER BY waiting DESC

Check Various Profile Options Enabled in R12

===================================================================================
You can display Profile Options within eBusiness Suite Release 11i and Release 12
both through the "System Administrator" responsibility (forms based) and also

through the "Functional Administrator" responsibility (OA Framework page) but
there may be times when you wish to quickly see all Profile Option values
outside of these screens

You can use the SQL below to query all settings for a particular profile option 

or profile options.

As this script is just performing a SELECT command, it is safe to run on any 
eBusiness Suite instance and has been tested for 11.5.10 and 12.0.x environments

Instructions
------------

1.  Connect to the eBusiness Suite database using APPS schema name 

2.  Execute the script listed below

3.  The script will prompt for you to enter a "profile_name"

Enter the value for the profile option name you wish to display

This value is case insensitive and is encased by % in the where clause

For example, if you enter the value  '%FND%Debug%' (do not enter the ' characters)
then you will see around 15 entries including:-

    FND: Debug Log Level
    FND_DEBUG_RULE_THRESHOLD

4.  If Oracle Support have asked you to run this script, then you should enable 
"spool" in SQLPLus to capture the output before running the script


NOTE - the REM commands are for documentation purposes only and can be ignored

set long 10000
set pagesize 500
set linesize 160
column SHORT_NAME format a30
column NAME       format a40
column LEVEL_SET  format a15
column CONTEXT    format a30
column VALUE      format a40

 
/* Formatted on 2010/11/02 15:17 (Formatter Plus v4.8.8) */
SELECT   p.profile_option_name short_name, n.user_profile_option_name NAME,
         DECODE (v.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Responsibility',
                 10004, 'User',
                 10005, 'Server',
                 'UnDef'
                ) level_set,
         DECODE (TO_CHAR (v.level_id),
                 '10001', '',
                 '10002', app.application_short_name,
                 '10003', rsp.responsibility_key,
                 '10005', svr.node_name,
                 '10006', org.NAME,
                 '10004', usr.user_name,
                 'UnDef'
                ) "CONTEXT",
         v.profile_option_value VALUE
    FROM fnd_profile_options p,
         fnd_profile_option_values v,
         fnd_profile_options_tl n,
         fnd_user usr,
         fnd_application app,
         fnd_responsibility rsp,
         fnd_nodes svr,
         hr_operating_units org
   WHERE n.user_profile_option_name LIKE '%&Profile_option%'
     AND p.profile_option_id = v.profile_option_id(+)
     AND p.profile_option_name = n.profile_option_name
     AND usr.user_id(+) = v.level_value
     AND rsp.application_id(+) = v.level_value_application_id
     AND rsp.responsibility_id(+) = v.level_value
     AND app.application_id(+) = v.level_value
     AND svr.node_id(+) = v.level_value
     AND org.organization_id(+) = v.level_value
--and n.user_profile_option_name IN ('&User_Profile_Option_Name')
ORDER BY short_name;

==============================================================

 /* Formatted on 2010/11/02 15:26 (Formatter Plus v4.8.8) */
SELECT   SUBSTR (e.profile_option_name, 1, 30) PROFILE,
         DECODE (a.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Responsibility',
                 10004, 'User'
                ) l,
         DECODE (a.level_id,
                 10001, 'Site',
                 10002, c.application_short_name,
                 10003, b.responsibility_name,
                 10004, d.user_name
                ) lvalue,
         NVL (a.profile_option_value, 'Is Null') VALUE,
         SUBSTR (a.last_update_date, 1, 25) updated_date
    FROM fnd_profile_option_values a INNER JOIN fnd_profile_options e
         ON a.profile_option_id = e.profile_option_id
         LEFT OUTER JOIN fnd_responsibility_tl b
         ON a.level_value = b.responsibility_id
         LEFT OUTER JOIN fnd_application c ON a.level_value = c.application_id
         LEFT OUTER JOIN fnd_user d ON a.level_value = d.user_id
   WHERE e.profile_option_name LIKE '%&Profile_option%'
ORDER BY profile_option_name;

===================================================================================

FIND Concurrent request details from OS PID or Oracle SID

For the particular Day:
=================
select * from fnd_concurrent_requests
where 1=1
and oracle_process_id=&OS_PID
and trunc(request_date)=trunc(

sysdate);

For all Days:
=========
select * from fnd_concurrent_requests
where 1=1
and oracle_process_id=&OS_PID

FULL Deatils from OS PID
====================
select * FROM v$process p, v$session s, fnd_concurrent_requests f
 WHERE s.paddr = p.addr /*and s.status = 'ACTIVE'*/
   AND s.username NOT LIKE '%SYS%'
   AND p.spid IN (SELECT oracle_process_id
                    FROM fnd_concurrent_requests
                   WHERE 1 = 1 AND oracle_process_id = &os_pid);

Complete Detials with filtering for the OS PID
===================================
SELECT trunc(f.ACTUAL_START_DATE) "Actual Start Date", s.LOGON_TIME,p.spid "OS PID", s.SID, s.serial#, s.action, s.username, s.status, s.program,
       p.program, s.module, s.lockwait, s.state, s.sql_hash_value,
       s.schemaname, s.osuser, s.machine, s.last_call_et, p.program,
       p.terminal, logon_time, module, s.osuser , f.request_id,  f.request_date,
        f.completion_text, f.outcome_product,
        f.logfile_node_name, f.outfile_name, argument_text,
       f.outfile_node_name, f.oracle_id, f.concurrent_program_id,
       f.responsibility_application_id, f.responsibility_id,
       f.last_update_login, f.nls_language, f.controlling_manager, f.actual_start_date,f.actual_completion_date
  FROM v$process p, v$session s, fnd_concurrent_requests f
  WHERE s.paddr = p.addr /*and s.status = 'ACTIVE'*/
   AND s.username NOT LIKE '%SYS%'
   AND p.spid=f.oracle_process_id
   order by actual_start_date desc;


Details for a specific OS PID
=======================

SELECT p.spid, s.SID, s.serial#, s.action, s.username, s.status, s.program "Session Program",
       p.program "Process Program, s.module, s.lockwait, s.state, s.sql_hash_value,
       s.schemaname, s.osuser, s.machine, s.last_call_et, p.program,
       p.terminal, logon_time, module, s.osuser , f.request_id,  f.request_date,
        f.completion_text, f.outcome_product,
        f.logfile_node_name, f.outfile_name, argument_text,
       f.outfile_node_name, f.oracle_id, f.concurrent_program_id,
       f.responsibility_application_id, f.responsibility_id,
       f.last_update_login, f.nls_language, f.controlling_manager, f.actual_start_date,f.actual_completion_date
  FROM v$process p, v$session s, fnd_concurrent_requests f
 WHERE s.paddr = p.addr /*and s.status = 'ACTIVE'*/
   AND s.username NOT LIKE '%SYS%'
   AND p.spid IN (SELECT oracle_process_id
                    FROM fnd_concurrent_requests
                   WHERE 1 = 1 AND oracle_process_id = &os_pid);

FIND ORACLE SID FROM OS PID

select spid, s.sid, p.serial#, s.terminal, pid, machine, s.program, osuser,
p.program
from v$session s, v$process p
where addr = paddr and (spid like
'%&&OSID%' or process like '%&OSID%');

or

select * from v$session where paddr in (select addr from v$process where spid =&OS_PID);
or

select p.spid,s.action,s.sid,s.

serial#,s.username,s.status, s.sql_hash_value,s.last_call_et,p.program,p.terminal,logon_time,module,s.osuser
from V$process p,V$session s
where s.paddr = p.addr and s.status = 'ACTIVE' and s.username not like '%SYS%'
AND SPID=&OS_PID;

or

-- show PROCESS id for all the active sessions
select p.spid,s.action,s.sid,s.serial#,s.username,s.status, s.sql_hash_value,s.last_call_et,p.program,p.terminal,logon_time,module,s.osuser
from V$process p,V$session s
where s.paddr = p.addr and s.status = 'ACTIVE' and s.username not like '%SYS%'
--AND SPID=&OS_PID;

FIND AND KILL BLOCKING SESSIONS IN ORACLE

1.  To find blocking session jobs below query will useful. It will return two rows.
select process,sid, blocking_session,SERIAL# from v$session where blocking_session is not null;
E.g.
SQL> select process,sid, blocking_session, serial# from v$session where blocking_session is not null;
PROCESS SID BLOCKING_SESSION SERIAL#
———— ———- —————- ———-
1234        365                       366      130


2.  Final step to kill the blocking session
alter system kill session ‘SID,SERIAL#’;
E.g.
SQL> alter system kill session '365,130';
System altered.

Query to find SQL AREA using Client OS User (or) Oracle DB user

select a.OSUSER, a.username, a.sid, a.serial#, b.sql_text
  from v$session a, v$sqlarea b
    where a.sql_address=b.address
    and a.OSUSER='&CLIENT_OS_USER';


select a.OSUSER, a.username, a.sid, a.serial#, b.sql_text
  from v$session a, v$sqlarea b
    where a.sql_address=b.address
    and a.username='&ORACLE_DB_USER';

select a.OSUSER, a.username, a.sid, a.serial#, b.sql_text
  from v$session a, v$sqlarea b
    where a.sql_address=b.address;

Query to find locked object in the database

select * from dba_objects
select do.object_name,sid,s.serial#,s.osuser,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, --s.session_id,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where s.ROW_WAIT_OBJ# = do.OBJECT_ID ;