The more useful scrips of manjunahta

There are few use full scrips for APPSDBA 
+++++++++++++++++++++++++++++++++++++++++++++

TO Know Active/inactive CM on Apps(to give value on ps -ef| grep FNDLIBR --USER NAME)

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 know the all Active Users list

set pages 300

set lines 100

col user_concurrent_program_name format a40 head PROGRAM trunc

col elapsed format 9999

col request_id format 9999999 head REQUEST

col user_name format a20

col oracle_process_id format a5 head OSPID

select  fcp.user_concurrent_program_name

,       fcr.request_id

,       round(24*60*( sysdate - actual_start_date )) elapsed

,       fu.user_name

,       fcr.oracle_process_id

from    apps.fnd_concurrent_requests fcr

,       apps.fnd_concurrent_programs_tl fcp

,       apps.fnd_user fu

where   fcp.concurrent_program_id = fcr.concurrent_program_id

and     fcp.application_id = fcr.program_application_id

and     fu.user_id = fcr.requested_by

and     fcr.phase_code = 'R'

and     fcp.language='US';

++++++++++++++++++++++++++++++

Find process ID for the concurrent request

set lines  200

column process heading "FNDLIBR PID"

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , 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.request_id = &Request_ID

AND a.phase_code = 'R';

++++++++++++++++++++++++++++++++++

First run following SQL

set lines 180

set pages 1000

set verify off

undef spid

column req_id format 99999999999

column OPID format a10

column PPID format a8

column SPID format a8

column ST_CD format a1

column ph_cd format a1

column CNAME format a30

column event format a15

column user_name format a10

column program format a8

column serial# format 999999

column sid format 9999

column username format a8

select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id "PPID",

e.user_concurrent_program_name "CNAME",

f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,

b.serial#,b.program,g.event,

to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,

to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE

from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,

                        c.program,d.spid from v$session c, v$process d

                        where c.paddr=d.addr) b,

                        apps.fnd_concurrent_programs_tl e,

                        apps.fnd_user f,

                        v$session_wait g

                        where a.oracle_process_id=b.spid

                        and a.concurrent_program_id=e.concurrent_program_id

                        and e.language='US'

                        and a.requested_by=f.user_id         

                        and b.sid=g.sid

                                                and a.status_code='R'

                                                and a.phase_code ='R';

++++++++++++++++++ Manjunatha +++++++++++++++++++++++++++