Find the EBS application url from the database

select * from icx_parameters

Find the application top server path from the database

Select Distinct Variable_Name  "TOP_NAME"
,Value  "PATH"
From Fnd_Env_Context Fec ,
Fnd_Concurrent_Processes Fcp,
fnd_concurrent_requests fcr
Where 1=1
And Fec.Concurrent_Process_Id=Fcp.Concurrent_Process_Id
And Fec.Concurrent_Process_Id=Fcr.Controlling_Manager
And Fec.Variable_Name ='&TOP_NAME'; -- AU_TOP FND_TOP CUSTOM_TOP etc...

Enable the debug profiles for concurrent program or API

For Concurrent Program:

FND: Debug Log Enabled YES 
FND: Debug Log Filename NULL 
FND: Debug Log Level STATEMENT   -- Unexpected, Error, Exception, Event, Procedure, Statement
FND: Debug Log Module %


SELECT log.module , log.message_text message
FROM fnd_log_messages log,
            fnd_log_transaction_context con
WHERE con.transaction_id = < request_id >
AND con.transaction_type = 'REQUEST'
AND con.transaction_context_id = log.transaction_context_id

ORDER BY log.log_sequence;


For API:

1) Execute below before API call:

fnd_global.apps_initialize(fnd_user_id, fnd_resp_id, fnd_appl_id);
fnd_profile.put('AFLOG_ENABLED', 'Y');
fnd_profile.put('AFLOG_MODULE', '%');
fnd_profile.put('AFLOG_LEVEL','1'); -- Level 1 is Statement Level

fnd_log_repository.init;

2) Find the current max log sequence value

SELECT MAX(LOG_SEQUENCE)

FROM FND_LOG_MESSAGES;

3) Call API

4) Execute below after API call:

fnd_global.apps_initialize(fnd_user_id, fnd_resp_id, fnd_appl_id);
fnd_profile.put('AFLOG_ENABLED', 'N');
fnd_profile.put('AFLOG_MODULE', '%');
fnd_profile.put('AFLOG_LEVEL','1'); -- Level 1 is Statement Level

fnd_log_repository.init;

5) Run the below query 

SELECT module, message_text

FROM fnd_log_messages

WHERE log_sequence > &max_log_from_step2

ORDER BY log_sequence;

Query to find the current running sql query of the concurrent program

select hash_value,sql_text
from v$sqltext
where hash_value in (select vs.sql_hash_value
                    from v$session vs
                    where vs.module like '%CONC_PRGO_SHORT_NAME%')
order by hash_value,piece;

Queries to find the DB locked object

select * from v$lock;

select C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE,B.sid,B.SERIAL#,B.STATUS,B.OSUSER,B.MACHINE
from V$LOCKED_OBJECT a
,    V$SESSION B
,    DBA_OBJECTS C
where 1=1
and   b.sid = a.session_id
and   a.OBJECT_ID = C.OBJECT_ID
;
select acc.*
from  DBA_DDL_LOCKS LOCKS
, V$ACCESS ACC
where 1=1
and LOCKS.session_id = ACC.sid
;
select * from  V$ACCESS;


select distinct B.sid,B.SERIAL#--c.object_id--C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE,B.sid,B.SERIAL#,B.STATUS,B.OSUSER,B.MACHINE
from V$LOCKED_OBJECT a
,    V$SESSION B
,    DBA_OBJECTS C
where 1=1
and   b.sid = a.session_id
and   a.OBJECT_ID = C.OBJECT_ID
--and c.object_id in (3384631,3384632)
and c.object_name = 'CONC_PROG_SHORT_NAME';

select acc.*
from  DBA_DDL_LOCKS LOCKS
, V$ACCESS ACC
where 1=1
and LOCKS.session_id = ACC.sid;

select * from  V$ACCESS;

SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = 'CONC_PROG_SHORT_NAME');

ALTER SYSTEM KILL SESSION 'SID,SERIALl#';
ALTER SYSTEM KILL SESSION '6247,5179';