Apps DBA Scripts

1. Provide Concurrent Program Name, It will list out all concurrent requests sets names that has concurrent program in it?

SELECT DISTINCT user_request_set_name
  FROM FND_REQUEST_SETS_TL
 WHERE request_set_id IN
          (SELECT request_set_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE concurrent_program_id =
                     (SELECT CONCURRENT_PROGRAM_ID
                        FROM fnd_concurrent_programs_tl
                       WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));

2. Provide Concurrent Request Set Name, It will list out all the concurrent programs It has?

SELECT USER_CONCURRENT_PROGRAM_NAME
  FROM fnd_concurrent_programs_tl
 WHERE CONCURRENT_PROGRAM_ID IN
          (SELECT concurrent_program_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE request_set_id =
                     (SELECT request_set_id
                        FROM FND_REQUEST_SETS_TL
                       WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));

3. Find out Inactive Form sessions for X hours?


set lines 200 pages 200
col event for a30

select a.sid,a.serial#,a.action,a.status,a.event,round(a.last_call_Et/60/60) LSTCALL_Hrs, 
round(a.seconds_in_wait/60/60) SCNDINWAIT_Hrs,
       a.process, b.spid
  from v$session a, v$process b
 where a.action like 'FRM%'
   and a.paddr = b.addr
   and a.last_call_Et/60/60 > &&No_Of_Hours_Old order by 6,5;

4. Find out Application Names (Products) in Oracle Apps?

set lines 200

set pagesize 300

col APPLICATION_NAME for a70

select a.APPLICATION_NAME, b.APPLICATION_SHORT_NAME from apps.fnd_application b, apps.fnd_application_tl a  where a.APPLICATION_ID=b.APPLICATION_ID; 

5. Find out What are all concurrent programs are assigned to What are all concurrent Managers?


set lines 200 pages 300

col USER_CONCURRENT_QUEUE_NAME for a50
col CONCURRENT_PROGRAM_NAME for a50

break on USER_CONCURRENT_QUEUE_NAME skip 1;
SELECT C.USER_CONCURRENT_QUEUE_NAME,B.CONCURRENT_PROGRAM_NAME,A.INCLUDE_FLAG
FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A, APPLSYS.FND_CONCURRENT_PROGRAMS B, APPS.FND_CONCURRENT_QUEUES_VL C
WHERE type_id = b.concurrent_program_id and c.concurrent_queue_id = a.concurrent_queue_id order by C.USER_CONCURRENT_QUEUE_NAME;

6. Find out session details  of a concurrent Request?


set lines 200 pages 300
col USER_CONCURRENT_PROGRAM_NAME for a40

select USER_CONCURRENT_PROGRAM_NAME,c.request_id,s.sid,p.spid,s.process,s.osuser,s.username,s.program,s.status,logon_time,last_call_et
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id
and request_id=&creq_id
/

7. Find out All Concurrent Queue/Manager sizes/Processes?

set lines 200 pages 300
select a.concurrent_queue_name,b.min_processes,b.max_processes from apps.fnd_concurrent_queues a,apps.fnd_concurrent_queue_size b where a.concurrent_queue_id=b.concurrent_queue_id;

8. Find out Responsibility name from a Concurrent program?

set lines 200 pages 300
SELECT DISTINCT
  FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
  APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE FRG.APPLICATION_ID=fapp.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE '%&Conc_Prog_name%'
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';

8. Find out Currently running concurrent requests with OSPID,SID and Serial# etc?

SELECT a.request_id, d.sid, d.serial# , c.SPID
    FROM apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_processes b,
    v$process c,
    v$session d
    WHERE a.controlling_manager = b.concurrent_process_id
    AND c.pid = b.oracle_process_id
    AND b.session_id=d.audsid
    AND a.phase_code = 'R' and status_code='R';

Oracle Apps DBA Important Tables

fnd_user

fnd_oracle_user_id

fnd_concurrent_requests

fnd_concurrent_programs_tr

fnd_concurrent_programs

fnd_responsibility

fnd_profile_option_values

fnd_menus

fnd_nodes

fnd_product_installations

fnd_application

fnd_product_groups

fnd_install_processes

ad_deferred_jobs

ad_applied_patches

ad_bugs

Current workflow mailer log file location

select fl.meaning,fcp.process_status_code, 
decode(fcq.concurrent_queue_name,'WFMLRSVC','maile r container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and
fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;

How to take the Backup of different types of Objects

col OWNER for a10
col OBJECT_NAME for a30
col OBJECT_TYPE for a20
set linesize 150
select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS, LAST_DDL_TIME from dba_objects where OBJECT_NAME like '&objectname';

Package Backup

set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool 'filename.pks'
select text from dba_source where name='objectname' and type='PACKAGE';
spool off
exit

Package Body Backup

set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool filename.pkb
select text from dba_source where name='objectname' and type='PACKAGE BODY';
spool off
exit


Backup of Triggers

set pagesize 0
set trimspool on
set heading off
set feedback off
set long 4000
spool filename.sql
select TRIGGER_BODY from dba_triggers where TRIGGER_NAME='&a' and OWNER='&a';
spool off


Backup of Function 

set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool filename.sql
select text from dba_source where name='objectname' and type='FUNCTION';
spool off
exit

View backup


Need to perform as sys user

set long 999999999
set pagesize 5000
spool filename.sql
select dbms_metadata.get_ddl('VIEW','objectname','ownerof the object') from dual;
spool off

Index backup


Need to perform as sys user

set long 999999999
set pagesize 5000
spool filename.sql
select dbms_metadata.get_ddl('INDEX','objectname','ownerof the object') from dual;
spool off

HOW TO SET THE OVERRIDE ADDRESS in WORKFLOW MAILER

Step 1: Connect to CM node as Apps user

Step 2: Check test email id

col PARAMETER_VALUE for a25

select fscpv.parameter_value
from fnd_svc_comp_params_tl fscpt,
fnd_svc_comp_param_vals fscpv
where fscpt.display_name = 'Test Address'
and fscpt.parameter_id = fscpv.parameter_id;

Step 3: Update with new test mail


update fnd_svc_comp_param_vals fscpv
set fscpv.parameter_value = '12345@abcdef.com'
where fscpv.parameter_id = 10057;


Commit;


Step 4: Check test email id

col PARAMETER_VALUE for a25

select fscpv.parameter_value
from fnd_svc_comp_params_tl fscpt,
fnd_svc_comp_param_vals fscpv
where fscpt.display_name = 'Test Address'
and fscpt.parameter_id = fscpv.parameter_id;


Step 5: Check from Front-end Also

Queries to get the SESSION INFORMATION

Checking  Timing details, Client PID of associated oracle SID

set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));


To Find Session Information Details based on SID or SPID or CLIENTPID

col program for a15F
col machine for a15
col terminal for a15
set lines 152

select s.sid,
s.serial#,
'*'||s.process||'*'  Client,
p.spid Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.action,
s.program || s.module,
s.terminal,
s.machine,
s.status,
--s.last_call_et
s.last_call_et/3600
from gv$session s, gv$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));



Checking Timing details, Client PID of associated oracle SID

undefine spid
set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999
select p.INST_ID,p.spid,s.sid, s.serial#, s.status, s.username, s.action,
to_char(s.logon_time, 'DD-MON-YY, HH24:MI') logon_time,
s.module,s.program,s.last_call_et/3600 last_call_et ,s.process cli_process,s.machine
cli_mach,s.sql_hash_value
from gv$session s, gv$process p
where p.addr=s.paddr and p.spid in(&SPID);

Checking Timing Details of SID and event waiting for

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME,
to_char(Sysdate, 'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a,
v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid;

Checking for active transactions SID

select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr and s.sid in(&SIDs);


Checking what is the Last SQL (input multiple sids)

undefine sid
col "Last SQL" for a70
select s.username, s.sid, s.serial#,t.sql_text "Last SQL"
from gv$session s, gv$sqlarea t
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.sid in (&SIDs);

All Active and Inactive connections

col program for a15F
col machine for a15
col terminal for a15
set lines 152

select s.sid,
s.serial#,
'*'||s.process||'*'  Client,
p.spid Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.action,
s.program || s.module,
s.terminal,
s.machine,
s.status,
--s.last_call_et
s.last_call_et/3600
from gv$session s, gv$process p
where p.addr=s.paddr and s.type != 'BACKGROUND';

col program for a15F
col machine for a15
col terminal for a15
set lines 152

select s.sid,
s.serial#,
'*'||s.process||'*'  Client,
p.spid Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.action,
s.program || s.module,
s.terminal,
s.machine,
s.status,
--s.last_call_et
s.last_call_et/3600
from gv$session s, gv$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
s.status='ACTIVE' and
--(s.last_call_et/3600)<1 and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));


Active sessions
select p.spid "Thread", s.sid "SID-Top Sessions",
substr(s.osuser,1,15) "OS User", substr(s.program,1,25) "Program Running"
from v$process p, v$session s
where p.addr=s.paddr
order by substr(s.osuser,1,15);


Session details from Session long ops

select SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME,username from
v$session_longops where sid=&SID and serial#=&SERIAL;


To list the nodes

set head off
set verify off
set echo off
set pages 1500
set linesize 70
prompt
prompt Environment sketch
prompt ==================================
select /*+ CHOOSE*/
'NODE_NAME.................: '||NODE_NAME,
'CREATION_DATE.............: '||CREATION_DATE,
'CREATED_BY ...............: '||CREATED_BY,
'SUPPORT_CP ...............: '||SUPPORT_CP,
'SUPPORT_FORMS ............: '||SUPPORT_FORMS,
'SUPPORT_WEB ..............: '||SUPPORT_WEB,
'SUPPORT_ADMIN ............: '||SUPPORT_ADMIN,
'STATUS ...................: '||STATUS,
'HOST.DOMAIN ..... ........: '||HOST||'.'||DOMAIN,
'SUPPORT_DB  ..............: '||SUPPORT_DB
from  apps.fnd_nodes;

Session details thru SPID

select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
from v$session where paddr in (select addr from v$process where spid = '11533');






Checking  Timing details, Client PID of associated oracle SID
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));


To list count of connections from other machines
select count(1),machine from gv$session where inst_id=2 group by machine;

To get total count of sessions and processes

select count(*) from v$session;

select count(*) from v$process;
select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;
To find sqltext thru sqladdress

select sql_address from v$session where sid=1999;

select sql_text from v$sqltext where ADDRESS='C00000027FF00AF0' order by PIECE;

To find sqltext for different sql hashvalue
select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785);

To list long running forms user sessions

select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;

To list inactive Sessions respective username

SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;

SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE';

SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS='INACTIVE';

To find session id with set of SPIDs
select sid from v$session, v$process where addr=paddr and spid in ('11555','26265','11533');

To find Sql Text given SQLHASH & SQLADDR
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece;

select piece,sql_text from v$sqltext where  ADDRESS ='&addr' order by piece;

To find Undo Generated For a given session
select  username,
t.used_ublk ,t.used_urec
from    gv$transaction t,gv$session s
where   t.addr=s.taddr and
s.sid='&sessionid';


***APPS 11i*****

To Find Forms User Session Details Given ClientProcess id

SELECT /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */
( SELECT SUBSTR ( fu.user_name, 1, 20 )
FROM apps.fnd_user fu
WHERE fu.user_id = fl.user_id
) user_name,
TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,
SUBSTR ( fl.process_spid, 1, 6 ) spid,
SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,
SUBSTR ( vs.process, 1, 8 ) f60webmx,
SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,
SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,
SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,
SUBSTR ( vs.module || ' - ' ||
( SELECT SUBSTR ( ft.user_form_name, 1, 40 )
FROM apps.fnd_form_tl ft
WHERE ft.application_id = rf.form_appl_id
AND ft.form_id = rf.form_id
and ft.language='US'
), 1, 40 ) form
FROM apps.fnd_logins fl,
gv$process vp,
apps.fnd_login_resp_forms rf,
gv$session vs
--fnd_form_tl ft
WHERE fl.end_time IS NULL
AND fl.start_time > sysdate - 31 /* login within last 7 days */
AND fl.login_type = 'FORM'
AND fl.process_spid = vp.spid
AND fl.pid = vp.pid
AND fl.login_id = rf.login_id
AND rf.end_time IS NULL
AND rf.audsid = vs.audsid
AND vs.process='&1'
ORDER BY
user_name,
login_start_time,
spid,
pid,
f60webmx,
sid,
serial#;

Checking Timing Details of SID and event waiting for

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi')
LOGON_TIME,
to_char(Sysdate, 'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from
v$session a,
v$session_wait b where a.sid=&sid and a.sid=b.sid;

Checking for active transactions SID
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr and
s.sid='&sessionid';
SQL> SQL> Enter value for sessionid: 219
old   1: select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where
t.addr=s.taddr and s.sid='&sessionid'
new   1: select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where
t.addr=s.taddr and s.sid='219';


Checking rollback/Undo segment info used by SID
column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'OS User' format a10
column te heading 'Terminal' format a10
SELECT r.name rr, nvl(s.username,'no transaction') us,s.sid, s.osuser os, s.terminal te, rs.rssize,
rs.xacts, rs.rssize/1048576 Rssize
FROM v$lock  l, v$session  s,v$rollname  r , v$rollstat rs
WHERE l.sid = s.sid(+) AND trunc(l.id1/65536) = r.usn AND l.type = 'TX' AND
l.lmode = 6   AND r.usn=rs.usn  and s.sid in (&sid_list_comma_sep);

Checking what is the Last SQL
undefine sid
col "Last SQL" for a70
select s.username, s.sid, t.sql_text "Last SQL"
from gv$session s, gv$sqlarea t
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.sid = '&sid';
Killing inactive sessions for more than 48hrs

set heading off
set feedback off
spool /PENVI/applcsf/prevent/scripts/kill_session.sql
SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
where last_call_et > 43200 and username is not null AND STATUS='INACTIVE';
spool off
exit

Session details complete (Input sid)
set echo off
set linesize 132
set verify off
set feedback off
set serveroutput on;
declare
SID number := 0 ;
inst_id number := 0 ;
SERIAL number := 0 ;
username varchar(20) := '';
Status varchar(8) := '';
machine varchar(10) := '';
terminal varchar(25) := '';
program varchar(30) := '';
Module varchar(30) := '';
Action varchar(20) := '';
sql_hash_value number := 0 ;
logontime varchar(30) := '';
last_call_et number := 0 ;
proc number := 0 ;
spid number := 0 ;
event varchar(30) := '';
state varchar(30) := '';
sql_texts varchar(1000) := '';
undo_size varchar (100) := 'N/A';
cursor cur1 is
select a.inst_id,a.sid sid,
a.serial# serial,
a.username username,
a.status status ,
a.machine machine,
a.terminal terminal,
a.program program,
a.module module,
a.action action,
a.sql_hash_value sql_hash_value,
to_char(a.logon_time,'DD-Mon-YYYY HH24:MI:SS') logontime,
round((a.last_call_et/60),2) last_call_et,
a.process proc,
b.spid spid,
event event,
state state
from gv$session a, gv$process b, gv$session_wait sw
where a.paddr=b.addr and a.inst_id=b.inst_id
and a.sid in (75)
and a.inst_id=sw.inst_id
and a.sid=sw.sid;
begin
for m in cur1
loop
DBMS_OUTPUT.ENABLE(25000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('INSTANCE ID....................... : ' || m.inst_id );
DBMS_OUTPUT.PUT_LINE('SID............................... : ' || m.sid );
DBMS_OUTPUT.PUT_LINE('SERIAL#........................... : ' || m.serial );
DBMS_OUTPUT.PUT_LINE('USERNAME.......................... : ' || m.username );
DBMS_OUTPUT.PUT_LINE('STATUS............................ : ' || m.status );
DBMS_OUTPUT.PUT_LINE('Client Machine.....................: ' || m.machine );
DBMS_OUTPUT.PUT_LINE('Terminal.......................... : ' || m.terminal);
DBMS_OUTPUT.PUT_LINE('Program........................... : ' || m.program );
DBMS_OUTPUT.PUT_LINE('Module............................ : ' || m.module );
DBMS_OUTPUT.PUT_LINE('Action............................ : ' || m.action );
DBMS_OUTPUT.PUT_LINE('SQL Hash Value.................... : ' || m.sql_hash_value );
DBMS_OUTPUT.PUT_LINE('Logon Time........................ : ' || m.logontime );
DBMS_OUTPUT.PUT_LINE('Last Call Et...................... : ' || m.last_call_et||' '||'min' );
DBMS_OUTPUT.PUT_LINE('ClientPID......................... : ' || m.proc );
DBMS_OUTPUT.PUT_LINE('ServerPID......................... : ' || m.spid );
DBMS_OUTPUT.PUT_LINE('Session Waiting for Event..........: ' || m.event );
DBMS_OUTPUT.PUT_LINE('Session state .....................: ' || m.state);
for rec_undo in (select nvl(t.used_ublk,0)||' '||'Blocks' undo_size from v$session s,v$transaction t where
s.taddr=t.addr(+) and
s.sid=m.sid )
loop
dbms_output.put_line('Undo Generation for sid is.........: ' ||rec_undo.undo_size);
end loop;
dbms_output.put_line('SQL_TEXT is..........:');
for rec in ( select sql_text sql_texts from v$session s,v$sqltext v where
s.sql_hash_value=v.hash_value and
s.sql_address=v.address and s.sid=m.sid order by piece)
loop
dbms_output.put_line(' '||rec.sql_texts);
end loop;
for n in ( select t.DISK_READS DISK_READS from gv$session s, gv$sqlarea t
where s.sql_hash_value =t.hash_value and s.sid=m.sid)
loop
dbms_output.put_line('Disk reads due to above SQL execution ' || n.DISK_READS);
end loop;
DBMS_OUTPUT.PUT_LINE(' ' );
DBMS_OUTPUT.PUT_LINE(':------------------------------------------------: ' );
DBMS_OUTPUT.PUT_LINE(' ' );

end loop;
end;
Session details complete (Input SPID)
set echo off
set linesize 132
set verify off
set feedback off
set serveroutput on;
declare
inst_id number := 0 ;
SID number := 0 ;
SERIAL number := 0 ;
username varchar(20) := '';
Status varchar(8) := '';
machine varchar(10) := '';
terminal varchar(25) := '';
program varchar(30) := '';
Module varchar(30) := '';
Action varchar(20) := '';
sql_hash_value number := 0 ;
logontime varchar(30) := '';
last_call_et number := 0 ;
proc number := 0 ;
spid number := 0 ;
event varchar(30) := '';
state varchar(30) := '';
sql_texts varchar(1000) := '';
undo_size varchar (100) := 'N/A';
cursor cur1 is
select a.inst_id, a.sid sid,
a.serial# serial,
a.username username,
a.status status ,
a.machine machine,
a.terminal terminal,
a.program program,
a.module module,
a.action action,
a.sql_hash_value sql_hash_value,
to_char(a.logon_time,'DD-Mon-YYYY HH24:MI:SS') logontime,
round((a.last_call_et/60),2) last_call_et,
a.process proc,
b.spid spid,
event event,
state state
from gv$session a, gv$process b, gv$session_wait sw
where a.paddr=b.addr and a.inst_id=b.inst_id
and b.spid in ( '&spid')
and a.inst_id=sw.inst_id
and a.sid=sw.sid;
begin
for m in cur1
loop
DBMS_OUTPUT.ENABLE(25000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('INSTANCE ID....................... : ' || m.inst_id );
DBMS_OUTPUT.PUT_LINE('SID............................... : ' || m.sid );
DBMS_OUTPUT.PUT_LINE('SERIAL#........................... : ' || m.serial );
DBMS_OUTPUT.PUT_LINE('USERNAME.......................... : ' || m.username );
DBMS_OUTPUT.PUT_LINE('STATUS............................ : ' || m.status );
DBMS_OUTPUT.PUT_LINE('Client Machine.....................: ' || m.machine );
DBMS_OUTPUT.PUT_LINE('Terminal.......................... : ' || m.terminal);
DBMS_OUTPUT.PUT_LINE('Program........................... : ' || m.program );
DBMS_OUTPUT.PUT_LINE('Module............................ : ' || m.module );
DBMS_OUTPUT.PUT_LINE('Action............................ : ' || m.action );
DBMS_OUTPUT.PUT_LINE('SQL Hash Value.................... : ' || m.sql_hash_value );
DBMS_OUTPUT.PUT_LINE('Logon Time........................ : ' || m.logontime );
DBMS_OUTPUT.PUT_LINE('Last Call Et...................... : ' || m.last_call_et||' '||'min' );
DBMS_OUTPUT.PUT_LINE('ClientPID......................... : ' || m.proc );
DBMS_OUTPUT.PUT_LINE('ServerPID......................... : ' || m.spid );
DBMS_OUTPUT.PUT_LINE('Session Waiting for Event..........: ' ||m.event );
DBMS_OUTPUT.PUT_LINE('Session state .....................: ' ||m.state);
for rec_undo in (select nvl(t.used_ublk,0)||' '||'Blocks' undo_size from v$session s,v$transaction t where
s.taddr=t.addr(+) and
s.sid=m.sid )
loop
dbms_output.put_line('Undo Generation for sid is.........: ' ||rec_undo.undo_size);
end loop;
dbms_output.put_line('SQL_TEXT is..........:');
for rec in ( select sql_text sql_texts from v$session s,v$sqltext v where
s.sql_hash_value=v.hash_value and
s.sql_address=v.address and s.sid=m.sid order by piece)
loop
dbms_output.put_line(' '||rec.sql_texts);
end loop;
for n in ( select t.DISK_READS DISK_READS from gv$session s, gv$sqlarea t
where s.sql_hash_value =t.hash_value and s.sid=m.sid)
loop
dbms_output.put_line('Disk reads due to above SQL execution ' || n.DISK_READS);
end loop;
DBMS_OUTPUT.PUT_LINE(' ' );
DBMS_OUTPUT.PUT_LINE(':------------------------------------------------: ' );
DBMS_OUTPUT.PUT_LINE(' ' );
end loop;end;

Count of JDBC thin client sessions grouped by status
col program for a15F
col machine for a15
col terminal for a15
set lines 152
select count(s.sid),s.status from gv$session s, gv$process p where p.addr=s.paddr and s.program || s.module like ('%JDBC Thin Client%') group by status;

JDBC Session count

select count(s.sid) from gv$session s where s.program || s.module like ('%JDBC Thin Client%');

Inactive sessions count

SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 1800
and username is not null
and module like '%JDBC Thin Client%'
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;

SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 43200
and username is not null
and module like '%JDBC Thin Client%'
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;


JDBC Sessions count
SELECT username,count(*) sess
FROM v$session
where username is not null
and module like '%JDBC Thin Client%'
group by username
order by sess DESC;

Machine wise count
select MACHINE, PROCESS,COUNT(*)
from V$SESSION
where program like '%JDBC%'
and username = 'APPS'
and process is not null
group by MACHINE, PROCESS
order by MACHINE ;

Inactive sessions count

SELECT count(*),module FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE' group by module;

SELECT username,status,count(*)sesion FROM v$session where username is not null and module
like '%JDBC Thin Client%' group by username ,status;

select count(status) Count, status, machine, program from v$session where program like '%JDBC%'
group by status, machine,program having status = 'INACTIVE' order by 1;

ACTIVE / INACTIVE Sessions
set linesize 132
set pagesize 100
col machine format a15
col OSuser format a12
col program format a30
SQL> select count(*) from v$session;

SQL> select count(*) from v$session where status='INACTIVE';

SQL> select count(*) from v$session where status='ACTIVE';

SQL> select machine, osuser, program, count(*) from v$session
group by machine, osuser, program order by 4 desc;

SQL> select count(status) Count, status, machine, program from v$session
where program like '%JDBC%' group by status, machine, program;

SQL> select count(status) Count, status, machine, module from v$session
where program = 'JDBC Thin Client' group by status, machine, module;

Logon time of JDBC

SELECT serial#, substr(program,1,20) program, status,
to_char(logon_time,'DD-MON-YY HH24:SS') Login_Time,
to_char(sysdate-last_call_et/86400,'DD-MON-YY HH24:SS') Last_Activity FROM
v$session
WHERE program like 'JDBC%' order by 4;

Session distribution

select to_char(sysdate,'DD/MM HH24:MI') "DATE",inst_id,count(inst_id) total_ses,sum(decode(status,'INACTIVE',1,0) ) inactive_ses from gv$session group by inst_id;

Program grouped by count of user connection
select unique s.program,s.osuser ,count(1) from v$session s, v$process p where s.username
is not null and s.paddr = p.addr and s.status='INACTIVE' group by s.program,s.osuser;

TOTAL Sessions/Inactive Sessions
select to_char(sysdate,'DD/MM HH24:MI') "DATE", inst_id, count(inst_id) total_ses,
sum(decode(status,'INACTIVE',1,0) )inactive_ses from gv$session group by inst_id
Thru Os user

col program for a15F
col machine for a15
col terminal for a15
set lines 152

select s.sid,
s.serial#,
'*'||s.process||'*'  Client,
p.spid Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.action,
s.program || s.module,
s.terminal,
s.machine,
s.status,
--s.last_call_et
s.last_call_et/3600
from gv$session s, gv$process p
where p.addr=s.paddr
and s.osuser='&osuser';
Session accessing an object

select b.sql_text,a.sid,a.serial#,b.users_executing, b.rows_processed,a.last_call_et/3600 Hrs from v$sqlarea b,v$session a where b.sql_text like '&object_name' and a.sql_address=b.address;


select b.sql_text,a.sid,a.serial#,b.users_executing, b.rows_processed,a.last_call_et/3600 Hrs from gv$sqlarea b,gv$session a where sql_text like '%DTEA_PA_REPORTING_AGT_HISTO%' and a.sql_address=b.address;

Select
a.session_id, b.sql_text,
count(*)
from
v$active_session_history a,v$sql b
where
a.session_state= 'ON CPU' and
a.SAMPLE_TIME > sysdate - (120/(24*60)) and a.sql_id=b.sql_id and b.sql_text like '%WF_ITEM_ATTRIBUTE_VALUES%'
group by a.session_id,b.sql_text
order by
count(*) desc;

Listing out details of program thru SQLID

Select
a.session_id, b.sql_text,a.program,a.module,a.action
from
v$active_session_history a,v$sql b
where
a.sql_id=b.sql_id and b.sql_id like '%fk9qzystpcazs%';

 How to find apps user when you know the o/s  pid in 11i for Forms users (f60webmx 100% CPU)


 You have to pass the UNIX process id to this script

 column "User Name" format a20
 column "ClPID" format a8
 select
 d.user_name "User Name",
 b.sid SID,b.serial# "Serial#", c.spid "srvPID", a.SPID "ClPID",
 to_char(START_TIME,'DD-MON-YY HH:MM:SS') "STime"
 from
 fnd_logins a, v$session b, v$process c, fnd_user d
 where
 b.paddr = c.addr
 and a.pid=c.pid
 and a.spid = b.process
 and d.user_id = a.user_id
 and (d.user_name = 'USER_NAME' OR 1=1)
 and a.SPID = &PID;