Monthly Archives: January 2011

Query to find number of archive logs getting generated every day


SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
SELECT
To_Char(First_Time,’YYYY-MM-DD’) DAY,
Count(1) Count#,
Min(RECID) Min#,
Max(RECID) Max#
FROM
v$log_history
GROUP
BY To_Char(First_Time,’YYYY-MM-DD’)
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B;

What Tables Are Involved In Using The System Profile ‘Sign-On:Audit Level’


Applies to:

Oracle Application Object Library – Version: 11.5.0 to 11.5.10.2 – Release: 11.5 to 11.5
Information in this document applies to any platform.
Checked for relevance on 29-MAY-2010

Goal

What tables are involved in using the system profile ‘Sign-On:Audit Level’?

Solution

There are three primary tables:

FND_LOGINS
FND_LOGIN_RESPONSIBILITIES
FND_LOGIN_RESP_FORMS

1. Least detailed level: User.
When the profile is set to User, the only table that gets updated is the table FND_LOGINS and only one record per user session.

2. Next level: Responsibility.
When the profile is set to Responsbility both FND_LOGINS and FND_LOGIN_RESPONSIBILITIES will be updated.
FND_LOGINS gets only one record per user session.
FND_LOGIN_RESPONSIBILITIES will be updated with one record for each
responsibility selected during the session.

3. Most detailed level: Form.
When the profile is set to Form all three tables are involved.
FND_LOGINS gets only one record per user session.
FND_LOGIN_RESPONSIBILITIES will be updated with one record for each
responsibility selected during the session.
FND_LOGIN_RESP_FORMS will be updated with one record for each form selected during the session.

Reference: Metalink Note 368260.1

Helpful SQL for Users Monitoring


— Current Forms Based Users in ERP

SELECT TIME, user_name, responsibility_name, user_form_name
FROM apps.fnd_form_sessions_v ORDER BY 1;

— Users Count Who Logged in to ERP

select count(distinct d.user_name)
from apps.fnd_logins a, v$session b, v$process c, apps.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);

— Users Who Logged in to ERP

select distinct d.user_name
from apps.fnd_logins a, v$session b, v$process c, apps.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);

Query to list of Responsibilities attached to a particular Oracle Application User


Hi Friends,

Kindly run below sql query as apps user for listing all the responsibilities attached to a particular user (prompted by query).

set pages 300 lines 300
col USER_NAME for a40
col RESPONSIBILITY_NAME for a50

select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = ‘&user_name’
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv(‘LANG’);

Happy Reading !!

Farhan Shaikh

Trace a Concurrent Request And Generate TKPROF File


Enable Tracing For The Concurrent Manager Program

Responsibility: System Administrator
Navigate: Concurrent > Program > Define
Query Concurrent Program
Select the Enable Trace Checkbox
Turn On Tracing
Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes
Run Concurrent Program With Tracing Turned On
Logon to the Responsibility that runs the Concurrent Program
In the Submit Request Screen click on Debug Options (B)
Select the Checkbox for SQL Trace

Find Trace File Name
Run the following SQL to find out the Raw trace name and location for the concurrent program. The SQL prompts the user for the request id

SELECT ’Request id: ’||request_id , ‘Trace id: ’||oracle_Process_id, ‘Trace Flag: ’||req.enable_trace, ‘Trace Name: ‘||dest.value||’/’||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,
‘Prog. Name: ’||prog.user_concurrent_program_name, ‘File Name: ’||execname.execution_file_name|| execname.subroutine_name , ‘Status : ’||decode(phase_code,’R’,’Running’) ||’-‘||decode(status_code,’R’,’Normal’), ‘SID Serial: ’||ses.sid||’,’|| ses.serial#, ‘Module : ’||ses.module from fnd_concurrent_requests req, v$session ses, v$process proc, v$parameter dest,
v$parameter dbnm, fnd_concurrent_programs_vl prog, fnd_executables execname where req.request_id = &request and req.oracle_process_id=proc.spid(+) and proc.addr = ses.paddr(+)
and dest.name=’user_dump_dest’ and dbnm.name=’db_name’ and req.concurrent_program_id = prog.concurrent_program_id and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id and prog.executable_id=execname.executable_id;

TKPROF Trace File
Once you have obtained the Raw trace file you need to format the file using TKPROF.

$tkprof raw_trace_file.trc output_file explain=apps/ sort=(exeela,fchela) sys=no

Where: raw_trace_file.trc: Name of trace file
output_file: tkprof out file
explain: This option provides the explain plan for the sql statements
sort: his provides the sort criteria in which all sql statements will be sorted. This will bring the bad sql at the top of the outputfile.
sys=no:Disables sql statements issued by user SYS

Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10″ long running queries

$ tkprof raw_trace_file.trc output_file sys=no explain=apps/ sort=’(prsela,exeela,fchela)’ print=10

Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use ‘schema.tablename’ with ‘explain=’ option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first ‘integer’ SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor

Enjoy Reading

Farhan Shaikh