Other apps scripts

ALL APPS Conc requests by USERNAME andor PROGRAM sorted by date DESC.txt
==========================================================================
alter session set nls_date_format='DD-MON-RR HH24:MI';
set lines 132
set trims on
set echo off
set feed on
set pages 60
col request_id heading "Request ID"
col requested_start_date heading "Start Date"
col user_name format a10 trunc heading "APPS User"
col prg_name format a30 trunc heading "Program Name"
col args format a45 trunc heading "Agruments [first 45 chars]"
select
reqs.request_id
, reqs.requested_start_date requested_start_date
, reqs.phase_code
, reqs.status_code
, usr.user_name
, prg.user_concurrent_program_name prg_name
, argument_text args
from apps.fnd_user usr
, apps.fnd_concurrent_requests reqs
, apps.fnd_concurrent_programs_tl prg
where usr.user_name like '&username'
--and reqs.phase_code IN ('P','R')
and reqs.concurrent_program_id = prg.concurrent_program_id
and prg.user_concurrent_program_name like '%&prog_like%'
and reqs.requested_by = usr.user_id
order by DECODE(reqs.phase_code,'R',1,'P',2,3)
, reqs.requested_start_date desc
, usr.user_name
/

analyze SCHEMA using fnd_stats.gather_schema_statistics.txt
=========================================================
undefine schema_to_analyze
set echo on
-- commit to avoid ORA-00164
commit;

begin fnd_stats.gather_schema_statistics('&&schema_to_analyze'); end;
/
alter session set nls_date_format='DD-MON-RRRR HH24:MI';
select count(*), last_analyzed
from dba_tables
where owner='&&schema_to_analyze'
group by last_analyzed
/
undefine schema_to_analyze


display session information for all oracle appslications.txt
=============================================================
--
set pages 0
set feedback off
set verify off
col fnd_name noprint new_value _owner.
select rtrim(owner)||'.' fnd_name
from dba_tables
where table_name = 'FND_USER';
grant create any synonym to system with admin option;
grant select any table to system with admin option;
drop synonym system.FND_LOGINS;
drop synonym system.FND_USER;
create synonym system.FND_LOGINS for &_owner.fnd_logins;
create synonym system.FND_USER for &_owner.fnd_user;

--
-- display all sessions within Oracle applications ONLY
--
set wrap off
set pagesize 9999
set linesize 132
set trims on
set feedback on
clear col
clear breaks
col sid_serial format a10 heading "Sid,Serial"
col o_user format a10 heading "-Oracle-|User Name"
col os_user format a10 heading "- OS -|User Name"
col logon format a15 heading "Login Time"
col idle format a15 heading "Idle"
col status format a10 heading "Status"
col apps_user format a10 heading "- Apps -|User Name"
col terminal format a15 trunc heading "- Apps -|Terminal"
col lockwait format a1 heading "L|o|c|k|w|a|i|t"
ttitle "Current Sessions In Oracle Applications"
select /*+ ORDERED */
chr(39)||s.sid||','||s.serial#||chr(39) sid_serial
, to_char(s.logon_time,'DDth - HH24:MI:SS') logon
, floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) IDLE
, s.username o_user
, s.osuser os_user
, s.status status
, u.user_name apps_user
--, n.TERMINAL_ID terminal
, DECODE(lockwait,'','','Y') lockwait
from v$session s
, v$process p
, fnd_logins n
, fnd_user u
where s.paddr = p.addr
and n.pid IS NOT NULL
and n.serial# IS NOT NULL
and n.serial# = p.serial#
and n.end_time IS NULL
and s.process = n.spid -- so we don't get hung sessions with old SID and SERIAL
and p.pid = n.pid
and n.user_id = u.user_id
order by u.user_name
, to_char(s.logon_time,'DDth - HH24:MI:SS')
/


enable trace for a session.txt
===============================
-- turn tracing ON or OFF for a given session
--
ttitle off
prompt
prompt +---------------------------------------------------------+
prompt | The Following Portion Will Turn tracing ON or OFF |
prompt | To exit early press CTL-C |
prompt +---------------------------------------------------------+
prompt
accept _sid number prompt "............................... Enter user SID :"
accept _serial number prompt "............................Enter user SERIAL# :"
accept _TRUE char prompt "Enter TRUE to enable trace or FALSE to disable :"
execute sys.dbms_system.set_sql_trace_in_session(&_sid,&_serial,&_TRUE);

__________________________________________________

find profile value from profile option.sql
============================================
Select Profile_Option_Value
from applsys.Fnd_Profile_Option_Values
Where
Level_ID = 10001
And Level_Value = 0
And Application_ID = 0
And Profile_Option_ID = ( Select Profile_Option_Id
From applsys.Fnd_Profile_Options
Where Profile_Option_Name = 'CONC_PMON_METHOD');


get application user from db sid.sql
======================================
rem to_char(max(l.start_time),'mm/dd/yy hh:mi:ssAM') startedat,
set lines 132
set verify off
col user_name head "Apps Signon" format a12 trunc
col description head "Real Name" format a25 trunc
rem col user_form_name head "Form Name" format a30 trunc
col forminfo head "Form Name" format a40 trunc
col time head "Elapsed Time" format a10
col zoom_level head "Zoom Level"
col startedat head "Logon At" format a19
col lastcallet format a11
accept trgtsid number prompt 'What is the SID : '
select /*+ rule */
to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM') startedat,
a.time,
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60) "LastCallET",
u.user_name, u.description ,
s.module || ' - ' || a.user_form_name forminfo
from applsys.fnd_logins l,
applsys.fnd_user u,
apps.fnd_signon_audit_view a,
v$process p,
v$session s
where s.sid = &trgtsid
and s.paddr = p.addr
and p.pid = l.pid
and l.end_time is null
and l.spid = s.process
and l.start_time is not null
-- and l.start_time = u.last_logon_date
-- and l.session_number = u.session_number
and l.user_id = u.user_id
and u.user_id = a.user_id
and p.pid = a.pid
and l.start_time = (select max(l2.start_time)
from applsys.fnd_logins l2
where l2.pid = l.pid)
group by to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM'),
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60),
u.user_name, u.description,a.time,s.module || ' - ' || a.user_form_name
order by to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM'),a.time;




locks
======================= SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,id1, id2, lmode, request, type,inst_id
FROM gV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM gV$LOCK WHERE request>0)
ORDER BY id1, request
/

Report profile options
=======================
clear col
clear breaks
set pages 9000
set lines 132
set verify off
col pov format a45 word_wrapped heading "Profile Value"
col lo format a5 heading "Level"
col lov format a40 heading "Value"
col pon noprint new_value n_pon
col upon noprint new_value n_upon
col sda noprint new_value n_sda
col eda noprint new_value n_eda
col cd noprint new_value n_cd
col cb noprint new_value n_cb
col d format a78 word_wrapped noprint new_value n_d
break on pon skip page
ttitle -
"Creation Date: " n_cd " Created By: " n_cb -
skip 1 -
"Date Active From:" n_sda " To:" n_eda -
skip 1 -
"Profile Option Name: " n_pon -
skip 1 -
"User Profile Name: " n_upon -
skip 1 -
"Profile Description: " -
skip 1 -
n_d -
skip 1 -
select lpad(fpo.profile_option_name,55) pon
, lpad(fpot.user_profile_option_name,55) upon
, fpot.description d
, lpad(fpo.start_date_active,15) sda
, lpad(fpo.end_date_active,15) eda
, lpad(fpo.creation_date,15) cd
, lpad(fu.user_name,20) cb
, 'Site' lo
, 'SITE' lov
, fpov.profile_option_value pov
from FND_PROFILE_OPTIONS_TL fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
where fpot.user_profile_option_name like '&&profile_like'
and fpot.profile_option_name = fpo.profile_option_name
and fpo.application_id = fpov.application_id
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpov.level_id = 10001 /* Site Level */
union all
select lpad(fpo.profile_option_name,55) pon
, lpad(fpot.user_profile_option_name,55) upon
, fpot.description d
, lpad(fpo.start_date_active,15) sda
, lpad(fpo.end_date_active,15) eda
, lpad(fpo.creation_date,15) cd
, lpad(fu.user_name,20) cb
, 'Apps' lo
, fa.application_name lov
, fpov.profile_option_value pov
from FND_PROFILE_OPTIONS_TL fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
, FND_APPLICATION_TL fa
where fpot.user_profile_option_name like '&&profile_like'
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpov.level_id = 10002 /* Application Level */
and fpov.level_value = fa.application_id
union all
select lpad(fpo.profile_option_name,55) pon
, lpad(fpot.user_profile_option_name,55) upon
, fpot.description d
, lpad(fpo.start_date_active,15) sda
, lpad(fpo.end_date_active,15) eda
, lpad(fpo.creation_date,15) cd
, lpad(fu.user_name,20) cb
, 'Resp' lo
, fr.responsibility_name lov
, fpov.profile_option_value pov
from FND_PROFILE_OPTIONS_TL fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
, FND_RESPONSIBILITY_TL fr
where fpot.user_profile_option_name like '&&profile_like'
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpov.level_id = 10003 /* Responsibility Level */
and fpov.level_value = fr.responsibility_id
and fpov.level_value_application_id = fr.application_id
union all
select lpad(fpo.profile_option_name,55) pon
, lpad(fpot.user_profile_option_name,55) upon
, fpot.description d
, lpad(fpo.start_date_active,15) sda
, lpad(fpo.end_date_active,15) eda
, lpad(fpo.creation_date,15) cd
, lpad(fu.user_name,20) cb
, 'User' lo
, fu2.user_name lov
, fpov.profile_option_value pov
from FND_PROFILE_OPTIONS_TL fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
, FND_USER fu2
where fpot.user_profile_option_name like '&&profile_like'
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpov.level_id = 10004 /* User Level */
and fpov.level_value = fu2.user_id
order by upon, lo, lov
/
undefine profile_like
ttitle off