APPS & DATABASE SCRIPTS:
This will give the number of users on the system in the past 1 hour:-
SQL> select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1/24 and user_id != '-1';
users
----------
4
This will give the number of users on the system in the past 1 day:-
SQL> select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1 and user_id != '-1';
users
----------
12
This will show the activity in the last 15 minutes:-
SQL> select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time", user_id, disabled_flag from icx_sessions where last_connect > sysdate - 1/96;
LIMIT_TIME LIMIT_CONNECTS Last Connection time USER_ID D
---------- -------------- --------------------------- ---------- -
999 9000 19-SEP-12 02:25:37 1013630 N
999 9000 19-SEP-12 02:12:50 1013691 Y
999 9000 19-SEP-12 02:12:58 6 N
999 9000 19-SEP-12 02:25:23 1013555 N
Total size of Database:-
SQL> SELECT ROUND(SUM(bytes/1024/1024/1024)) FROM dba_data_files;
ROUND(SUM(BYTES/1024/1024/1024))
--------------------------------
184
Free size of database:-
SQL> SELECT /*+ FIRST_ROWS*/ROUND(SUM(bytes/1024/1024/1024)) FROM dba_free_space;
ROUND(SUM(BYTES/1024/1024/1024))
--------------------------------
38
All Invalid objects in database:-
SQL> SELECT COUNT(*) FROM all_objects WHERE Status = 'INVALID';
COUNT(*)
----------
641
Total Blocking Session:-
SQL> SELECT count(*) FROM v$lock WHERE BLOCK='1';
COUNT(*)
----------
0
Total Locked objects:-
SQL> SELECT COUNT(*) FROM v$locked_object;
COUNT(*)
----------
1
Total no of session :-
SQL> SELECT count(*) FROM v$session;
COUNT(*)
----------
105
Total no of concurrent Process:-
SQL> SELECT COUNT(*) FROM apps.fnd_concurrent_processes;
COUNT(*)
----------
672
Total no of concurrent request:-
SQL> SELECT COUNT(*) FROM apps.fnd_concurrent_requests;
COUNT(*)
----------
21681
Running Concurrent Requests and scheduled Concurrent Requests:-
SQL> SELECT phase_code,COUNT(*) FROM apps.fnd_concurrent_requests WHERE phase_code IN('P','R') GROUP BY phase_code ORDER BY 1 DESC;
P COUNT(*)
- ----------
P 4
Last day total no. of concurrent requests:-
SQL> SELECT sysdate -1 , count(*) FROM apps.fnd_concurrent_requests WHERE to_char(actual_completion_date,'YYYYMMDD') =(SELECT to_char(sysdate -1,'YYYYMMDD') FROM dual);
SYSDATE-1 COUNT(*)
------------------ ----------
To clear the jsp & modplsql caches run this command:-
[oracle@r0469 ~]$ rm -rf $OA_HTML/_page/*
[oracle@r0469 ~]$ rm -rf $COMMON_TOP/_page/*
[oracle@r0469 ~]$ rm -rf $IAS_ORACLE_HOME/apache/
But in R12 the command to stop and start apache only takes action on Apache web server and not on the OC4J container that executes the JVM.
To bounce the OC4J container there is a specific script:
$INST_TOP/admin/scripts/adoaco
Therefore when making modification to JAVA class in R12 for the web application, in order to have that change recognized, it is needed to bounce OC4J using adoacorectl.sh script.
$INST_TOP/admin/scripts/adoaco
Therefore when making modification to JAVA class in R12 for the web application, in order to have that change recognized, it is needed to bounce OC4J using adoacorectl.sh script.
SCRIPT TO KNOW “ACTIVE USERS” FOR OACOREGROUP
REM
REM SQL to count number of Apps 11i users
REM Run as APPS user
REM
select ‘Number of user sessions : ‘ || count( distinct session_id) How_many_user_sessions from icx_sessions icx where disabled_flag != ‘Y’ and PSEUDO_FLAG = ‘N’ and (last_connect + decode(FND_PROFILE.VALUE(‘ICX_SESSION_TIMEOUT’), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE(‘ICX_SESSION_TIMEOUT’)/60)/24) > sysdate and counter < limit_connects;
REM
REM END OF SQL
REM
HOW TO DETERMINE “ACTIVE FORMS USERS” FOR FORMSGROUP
Check the number of f60webmx processes on the Middle Tier server.
For example:
ps -ef | grep f60webx | wc -l
Oracle Tuning And Diagnostics Script — Active Session Info
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = ‘ACTIVE’
ORDER BY a.spid, c.piece
Trace SQL Query Average Execution Time Using SQL ID
SELECT sql_id, child_number, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text
FROM v$sql s
WHERE s.sql_id=’4n01r8z5hgfru’
Get The Detail Explain Plan Using SQL ID
SELECT plan_table_output FROM TABLE(dbms_xplan.display_cursor(‘dtdqt19kfv6yx’))
Session Elapsed Processing Time
SELECT s.sid, s.username, s.module,
round(t.VALUE/1000000,2) “Elapsed Processing Time (Sec)”
FROM v$sess_time_model t, v$session s
WHERE t.sid = s.sid
AND t.stat_name = ‘DB time’
AND s.username IS NOT NULL
AND t.VALUE/1000000 >= ’1′ –running more than 1 second
ORDER BY round(t.VALUE/1000000,2) DESC
Session Elapsed Processing Time Statistic By SID
SELECT a.sid, b.username, a.stat_name, ROUND((a.VALUE/1000000),2) “Time (Sec)”
FROM v$sess_time_model a, v$session b
WHERE a.sid = b.sid
AND b.sid = ’194′
ORDER BY ROUND((a.VALUE/1000000),2) DESC
Use Longops To Check The Estimation Query Runtime
SELECT sid, serial#, opname, target, sofar, totalwork, units, start_time,
last_update_time, time_remaining “REMAIN SEC”, round(time_remaining/60,2) “REMAIN MINS”,
elapsed_seconds “ELAPSED SEC”, round(elapsed_seconds/60,2) “ELAPSED MINS”,
round((time_remaining+elapsed_seconds)/60,2)”TOTAL MINS”, message TIME
FROM v$session_longops
WHERE sofar<>totalwork
AND time_remaining <> ’0′
Detect Blocking Session
SELECT sid, serial#, username, STATUS, state, event,
blocking_session, seconds_in_wait, wait_time, action, logon_time
FROM gv$session
WHERE state IN (‘WAITING’)
AND wait_class != ‘Idle’
AND event LIKE ‘%enq%’
AND TYPE=’USER’
Active Table Locking
SELECT b.sid, b.serial#, b.program, b.osuser, b.machine, b.TYPE, b.action,
c.sql_text,b.logon_time, e.owner, e.object_name “Table Lock”
FROM v$session b, v$sqltext c, v$locked_object d, dba_objects e
WHERE b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = ‘ACTIVE’
ORDER BY b.sid, c.piece
RAC Active Table Locking
SELECT b.sid, b.serial#, a.spi0A
SELECT sid, serial#, username, STATUS, state, event,
blocking_session, seconds_in_wait, wait_time, action, logon_time
FROM gv$session
WHERE state IN (‘WAITING’)
AND wait_class != ‘Idle’
AND event LIKE ‘%enq%’
AND TYPE=’USER’
Active Table Locking
SELECT b.sid, b.serial#, b.program, b.osuser, b.machine, b.TYPE, b.action,
c.sql_text,b.logon_time, e.owner, e.object_name “Table Lock”
FROM v$session b, v$sqltext c, v$locked_object d, dba_objects e
WHERE b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = ‘ACTIVE’
ORDER BY b.sid, c.piece
RAC Active Table Locking
SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text,
b.state, c.sql_text,b.logon_time,
b.STATUS, e.owner, e.object_name “Table Lock”
FROM gv$process a, gv$session b, gv$sqltext c, gv$locked_object d, dba_objects e
WHERE a.addr=b.paddr
AND b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = ‘ACTIVE’
ORDER BY a.spid, c.piece
Monitor Highest SQL Wait Time Using Active Session History (ASH)
SELECT h.session_id, h.session_serial#, h.sql_id, h.session_state,
h.blocking_session_status, h.event, e.wait_class, h.module, u.username, SQL.sql_text,
SUM(h.wait_time + h.time_waited) “Total Wait Time (ms)”
FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e
WHERE h.sample_time BETWEEN sysdate – 1/24 AND sysdate –event in the last hour
AND h.sql_id = SQL.sql_id
AND h.user_id = u.user_id
AND h.event# = e.event#
GROUP BY h.session_id, h.session_serial#, h.sql_id, h.session_state,
h.blocking_session_status, h.event, e.wait_class, h.module, u.username, SQL.sql_text
ORDER BY SUM(h.wait_time + h.time_waited) DESC
Monitor Highest Object Wait Time Using Active Session History (ASH)
SELECT o.owner, o.object_name, o.object_type, h.session_id, h.session_serial#,
h.sql_id, h.module, SUM(h.wait_time + h.time_waited) “Total Wait Time (ms)”
FROM v$active_session_history h, dba_objects o, v$event_name e
WHERE h.sample_time BETWEEN sysdate – 1/24 AND sysdate –event in the last hour
AND h.current_obj# = o.object_id
AND e.event_id = h.event_id
GROUP BY o.owner, o.object_name, o.object_type, h.session_id, h.session_serial#,
h.sql_id, h.module
ORDER BY SUM(h.wait_time + h.time_waited) DESC
Monitor Highest Event Wait Time Using Active Session History (ASH)
SELECT h.event “Wait Event”, SUM(h.wait_time + h.time_waited) “Total Wait Time (ms)”
FROM v$active_session_history h, v$event_name e
WHERE h.sample_time BETWEEN sysdate – 1/24 AND sysdate –event in the last hour
AND h.event_id = e.event_id
AND e.wait_class <> ‘Idle’
GROUP BY h.event
ORDER BY SUM(h.wait_time + h.time_waited) DESC
Database Time Model Statistic
SELECT wait_class, NAME, ROUND (time_secs, 2) “Time (Sec)”,
ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
FROM
(SELECT n.wait_class, e.event NAME, e.time_waited / 100 time_secs
FROM v$system_event e, v$event_name n
WHERE n.NAME = e.event
AND n.wait_class <> ‘Idle’
AND time_waited > 0
UNION
SELECT
‘CPU’,
‘Server CPU’,
SUM (VALUE / 1000000) time_secs
FROM v$sys_time_model
WHERE stat_name IN (‘background cpu time’, ‘DB CPU’))
ORDER BY time_secs DESC;
Monitor I/O On Data Files
SELECT vfs.file#, dbf.file_name, dbf.tablespace_name, dbf.bytes, vfs.phyrds/vfs.phywrts,
vfs.phyblkrd/vfs.phyblkwrt, vfs.readtim, vfs.writetim
FROM v$filestat vfs, dba_data_files dbf
WHERE vfs.file# = dbf.file_id
I/O Stats For Data Files & Temp Files
SELECT file_no,
filetype_name,
small_sync_read_reqs “Synch Single Block Read Reqs”,
small_read_reqs “Single Block Read Requests”,
small_write_reqs “Single Block Write Requests”,
round(small_sync_read_latency/1000,2) “Single Block Read Latency (s)”,
large_read_reqs “Multiblock Read Requests”,
large_write_reqs “Multiblock Write Requests”,
async_io “Asynch I/O Availability”
FROM v$iostat_file
WHERE filetype_id IN (2,6) –data file and temp file
I/O Stats By Functionality
SELECT function_name,
small_read_reqs “Single Block Read Requests”,
small_write_reqs “Single Block Write Requests”,
large_read_reqs “Multiblock Read Requests”,
large_write_reqs “Multiblock Write Requests”,
number_of_wait “I/O Waits”,
round(wait_time/1000,2) “Total Wait Time (ms)”
FROM v$iostat_function
ORDER BY function_name
Temporary Tablespace Usage By SID
SELECT tu.username, s.sid, s.serial#, s.sql_id, s.sql_address, tu.segtype,
tu.extents, tu.blocks, SQL.sql_text
FROM v$tempseg_usage tu, v$session s, v$sql SQL
WHERE tu.session_addr = s.addr
AND tu.session_num = s.serial#
AND s.sql_id = SQL.sql_id
AND s.sql_address = SQL.address
Monitor Overall Oracle Tablespace
SELECT d.STATUS “Status”,
d.tablespace_name “Name”,
d.contents “Type”,
d.extent_management “Extent Management”,
d.initial_extent “Initial Extent”,
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),’99,999,990.900′) “Size (M)”,
TO_CHAR(NVL(a.bytes – NVL(f.bytes, 0), 0)/1024/1024,’99,999,999.999′) “Used (M)”,
TO_CHAR(NVL((a.bytes – NVL(f.bytes, 0)) / a.bytes * 100, 0), ’990.00′) “Used %”,
TO_CHAR(NVL(a.maxbytes / 1024 / 1024, 0),’99,999,990.900′) “MaxSize (M)”,
TO_CHAR(NVL((a.bytes – NVL(f.bytes, 0)) / a.maxbytes * 100, 0), ’990.00′) “Used % of Max”
FROM sys.dba_tablespaces d,
(SELECT tablespace_name,
SUM(bytes) bytes,
SUM(decode(autoextensible,’NO’,bytes,’YES’,maxbytes))
maxbytes FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
ORDER BY 10 DESC;
Cache Hit Ratio
SELECT ROUND(((1-(SUM(DECODE(name,
‘physical reads’, VALUE,0))/
(SUM(DECODE(name, ‘db block gets’, VALUE,0))+
(SUM(DECODE(name, ‘consistent gets’, VALUE, 0))))))*100),2)
|| ‘%’ “Buffer Cache Hit Ratio”
FROM v$sysstat –Use gv$sysstat if running on RAC environment
Library Cache Hit Ratio
SELECT SUM(pins) “Total Pins”, SUM(reloads) “Total Reloads”,
SUM(reloads)/SUM(pins) *100 libcache
FROM v$librarycache –Use v$librarycache if running on RAC environment
DB Session Memory Usage
SELECT se.sid,n.name, MAX(se.VALUE) maxmem
FROM v$sesstat se, v$statname n
WHERE n.statistic# = se.statistic#
AND n.name IN (‘session pga memory’,'session pga memory max’,
‘session uga memory’,'session uga memory max’)
GROUP BY n.name, se.sid
ORDER BY MAX(se.VALUE)
You can query this for how much free, used, total, and percentage filled space is available for each tablespace
select tablespace_name, ROUND(bytes_free,2) free,ROUND(bytes_total-bytes_free,2) used, ROUND(bytes_total,2) total,perused
from (select a.tablespace_name, a.bytes_free,b.bytes_total,
ROUND((100-(100*a.bytes_free/b.bytes_total)),2) perused from
(Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a,
(Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b
where a.tablespace_name=b.tablespace_name) where perused >= 0 ORDER BY tablespace_name;
select tablespace_name,sum(bytes)/(1024*1024) from dba_free_space
group by tablespace_name;
select tablespace_name, ROUND(bytes_free,2) free,ROUND(bytes_total-bytes_free,2) used, ROUND(bytes_total,2) total,perused
from (select a.tablespace_name, a.bytes_free,b.bytes_total,
ROUND((100-(100*a.bytes_free/b.bytes_total)),2) perused from
(Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a,
(Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b
where a.tablespace_name=b.tablespace_name) where perused >= 0 ORDER BY tablespace_name;
select tablespace_name,sum(bytes)/(1024*1024) from dba_free_space
group by tablespace_name;
How to check apps HTTP_Server,OC4J status/Start/Stop :-
[oracle@r0469 scripts]$ ./adopmnctl.sh status
[oracle@r0469 scripts]$ ./adopmnctl.sh start
[oracle@r0469 scripts]$ ./adopmnctl.sh stop
You are running adopmnctl.sh version 120.6
Checking status of OPMN managed processes...
Processes in Instance: VIS_r0469.r0469.oracle.com
---------------------------------+--------------------+---------+---------
ias-component | process-type | pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group | OC4J:oafm | 12248 | Alive
OC4JGroup:default_group | OC4J:forms | 12182 | Alive
OC4JGroup:default_group | OC4J:oacore | 12100 | Alive
HTTP_Server | HTTP_Server | 12048 | Alive
adopmnctl.sh: exiting with status 0
adopmnctl.sh: check the logfile /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adopmnctl.txt for more information ...
How to check apps Concurrent Manager status/Start/Stop :-
[oracle@r0469 scripts]$ ./adcmctl.sh start
[oracle@r0469 scripts]$ ./adcmctl.sh stop
[oracle@r0469 scripts]$ ./adcmctl.sh status
You are running adcmctl.sh version 120.17.12010000.3
Enter the APPS username : apps
Enter the APPS password :apps
Internal Concurrent Manager is Active.
adcmctl.sh: exiting with status 0
adcmctl.sh: check the logfile /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adcmctl.txt for more information ...
R12 Apps Scripts Locations:-
[oracle@r0469 ]$ cd /oracle/VIS/inst/apps/VIS_r0469/admin/scripts
[oracle@r0469 scripts]$ ll
total 176
-rwx------ 1 oracle dba 7301 Aug 31 12:33 adalnctl.sh
-rwx------ 1 oracle dba 8243 Aug 31 12:34 adapcctl.sh
-rwx------ 1 oracle dba 1540 Aug 31 12:33 adautocfg.sh
-rwx------ 1 oracle dba 16497 Aug 31 12:33 adcmctl.sh
-rwx------ 1 oracle dba 7215 Aug 31 12:33 adexecsql.pl
-rwx------ 1 oracle dba 8728 Aug 31 12:34 adformsctl.sh
-rwx------ 1 oracle dba 10208 Aug 31 12:34 adformsrvctl.sh
-rwx------ 1 oracle dba 7996 Aug 31 12:34 adoacorectl.sh
-rwx------ 1 oracle dba 8004 Aug 31 12:34 adoafmctl.sh
-rwx------ 1 oracle dba 7282 Aug 31 12:34 adopmnctl.sh
-rwx------ 1 oracle dba 15298 Aug 31 12:33 adpreclone.pl
-rwx------ 1 oracle dba 5801 Aug 31 12:33 adstpall.sh
-rwx------ 1 oracle dba 5945 Aug 31 12:33 adstrtal.sh
-rwx------ 1 oracle dba 2240 Aug 31 12:34 gsmstart.sh
drwxrwxrwx 2 oracle dba 4096 May 5 14:53 ieo
-rwx------ 1 oracle dba 2277 Aug 31 12:34 java.sh
-rwx------ 1 oracle dba 6388 Aug 31 12:36 jtffmctl.sh
drwxrwxrwx 2 oracle dba 4096 May 5 14:53 msc
-rwxrwxr-- 1 oracle dba 6632 Aug 31 12:36 mwactl.sh
-rwxrwxr-- 1 oracle dba 6645 Aug 31 12:36 mwactlwrpr.sh
-rwxrwxrwx 1 oracle dba 1438 May 16 11:56 sqlnet.log
How to check apps Listener Status/Start/Stop:-
[oracle@r0469 scripts]$ ./adalnctl.sh status
[oracle@r0469 scripts]$ ./adalnctl.sh start
[oracle@r0469 scripts]$ ./adalnctl.sh stop
adalnctl.sh version 120.3
Checking status for listener process APPS_VIS.
LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 20-SEP-2012 12:52:08
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=r0469)(Port=1626))
STATUS of the LISTENER
------------------------
Alias APPS_VIS
Version TNSLSNR for Linux: Version 10.1.0.5.0 - Production
Start Date 20-SEP-2012 09:47:09
Uptime 0 days 3 hr. 4 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/VIS/inst/apps/VIS_r0469/ora/10.1.2/network/admin/listener.ora
Listener Log File /oracle/VIS/inst/apps/VIS_r0469/logs/ora/10.1.2/network/apps_vis.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=r0469.oracle.com)(PORT=1626)))
Services Summary...
Service "FNDFS" has 1 instance(s).
Instance "FNDFS", status UNKNOWN, has 1 handler(s) for this service...
Service "FNDSM" has 1 instance(s).
Instance "FNDSM", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
adalnctl.sh: exiting with status 0
adalnctl.sh: check the logfile
/oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adalnctl.txt for more information ...
Database and Application Information
Below are some of the queries that can be used to get the database and Application information.
1) Get Product Version
SQL> SELECT product
, VERSION
, status
FROM product_component_version;
2) Get Applications Version and Patch Information
SQL> SELECT SUBSTR (a.application_name, 1, 60) Application_Name
, SUBSTR (i.product_version, 1, 4) Version
, i.patch_level
, i.application_id
, i.last_update_date
FROM apps.fnd_product_installations i
, apps.fnd_application_all_view a
WHERE i.application_id = a.application_id
ORDER BY a.application_name;
3) Patch Information AD_APPLIED_PATCHES table stores information about all the patches installed in the system.
SQL> SELECT applied_patch_id
, patch_name
, patch_type
, source_code
, creation_date
, last_update_date
FROM ad_applied_patches;
4) Check if the application is setup for Multi-Org
SQL> SELECT multi_org_flag
FROM fnd_product_groups;
M
-
Y
Add Responsibility using Sqlplus:
BEGIN
FND_USER_RESP_GROUPS_API.LOAD_ROW
(X_USER_NAME => 'ANATESH',
X_RESP_KEY => 'APPLICATION_DEVELOPER',
X_APP_SHORT_NAME => 'FND',
X_SECURITY_GROUP => 'STANDARD',
X_OWNER => 'SYSADMIN',
X_START_DATE => Trunc(sysdate),
X_END_DATE => NULL,
X_DESCRIPTION => NULL,
X_LAST_UPDATE_DATE => Trunc(sysdate));
COMMIT;
END
Query to List all the responsibilities attached to a User
He needed query that can list all the responsibilities attached to a user.
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_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG');
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_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG');
Script to delete the concurrent program from Oracle
fnd_program.delete_program('program short name','schema');
fnd_program.delete_executable('program short name','schema');
commit;
End;
fnd_program.delete_executable('program short name','schema');
commit;
End;
Find out the URL of the Application in the database
If you want to find out the URL of the Application in the database in which table you can check ?
Option 1:
SQL> select * from icx_parameters;
Option 2:
SELECT PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES
WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT')
AND LEVEL_VALUE=0;
Option 1:
SQL> select * from icx_parameters;
Option 2:
SELECT PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES
WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT')
AND LEVEL_VALUE=0;
SCRIPT TO DISPLAY STATUS OF ALL THE CONCURRENT MANAGERS
SELECT DISTINCT concurrent_process_id "Concurrent Process ID",
pid "System Process ID", os_process_id "Oracle Process ID",
q.concurrent_queue_name "Concurrent Manager Name",
p.process_status_code "Status of Concurrent Manager",
TO_CHAR(p.process_start_date,'MM-DD-YYYY HH:MI:SSAM') "Concurrent Manager Started at"
FROM fnd_concurrent_processes p,
fnd_concurrent_queues q,
fnd_v$process
WHERE q.application_id = queue_application_id
AND q.concurrent_queue_id = p.concurrent_queue_id
AND spid = os_process_id
AND process_status_code NOT IN ('K', 'S')
ORDER BY concurrent_process_id, os_process_id, q.concurrent_queue_name;
Query to find who and when update an Oracle Application user's profile
SELECT t.user_profile_option_name, profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date v.
last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;
v.creation_date,
v.last_update_date,
v.creation_date v.
last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;
Query to fetch Responsibilities attached to a user:
The below query will fetch the responsibilities assigned to a particular user.
fu.user_id,
fu.user_name,
fr.responsibility_name,
fr.description,
fa.application_name
FROM fnd_user fu,
fnd_user_resp_groups g,
fnd_application_tl fa,
fnd_responsibility_tl fr
WHERE
g.user_id(+) = fu.user_id
AND g.responsibility_application_id = fa.application_id
AND fa.application_id = fr.application_id
AND g.responsibility_id = fr.responsibility_id
AND fu.user_name =UPPER('User_Name');
How to get Login Passwords for application & Data Base instances
Most often, oracle apps developers have to work in different DB instances for development as well as for testing purposes. In such situations we need to get access to different oracle application instances and data base (dev/test/crp etc.,) instances. So we have to request & follow a lengthy approval process to get those login details, some times you may end up in loosing your delivery schedules.
There is a simple way by which you can get the logins/passwords of your DB & Application with out chasing the DBAs.
Oracle follows an encryption algorithm to encrypt user passwords. Most references to the encryption algorithm point to either the PL/SQL package APPS.FND_WEB_SEC or the Java class "oracle.apps.fnd.security.WebSessionManagerProc".
For decryption and encryption, the following calls are made:
APPS.FND_WEB_SEC >
oracle.apps.fnd.security.WebSessionManagerProc>
oracle.apps.fnd.security.AolSecurity>
oracle.apps.fnd.security.AolSecurityPrivate
The actual encryption and decryption routines are in the "oracle.apps.fnd.security.AolSecurityPrivate" Java class. This Java class is stored both in the database as a Java Stored Procedure and in the operating system directory $COMMON_TOP/java.
Create a package specification: get_pwd
-- Package Specification
CREATE OR REPLACE PAGE get_pwd AS
FUNCTION decrypt (KEY IN VARCHAR2,VALUE IN VARCHAR2)RETURN VARCHAR2;
END get_pwd;
Create the package body: get_pwd
-- Package Body
CREATE OR REPLACE PAGE BODY get_pwd AS
FUNCTION decrypt (KEY IN VARCHAR2,VALUE IN VARCHAR2)RETURN VARCHAR2 AS
LANGUAGE JAVA
NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
Call the package function as shown below:
/** Run this on toad, Get the DB apps password */
SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
FROM DUAL)), usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')
,1
, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')
- 1
)
FROM DUAL))
Call the package function as shown below:
/** Run this on toad, Get the application usernames and passwords */
SELECT usertable.user_name
, (SELECT get_pwd.decrypt (UPPER ((SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
FROM DUAL)), usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE
UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')
,1
, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')
- 1
)
FROM DUAL))))
,usertable.encrypted_user_password)
FROM DUAL) AS encrypted_user_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ('username') -- Here username is application login such as 'OPERATIONS'
Concurrent Program Monitoring Scripts
Most frequently in support projects we need to monitor and track the concurrent programs. Here are some the important queries to track the status of concurrent program. These are also very useful for the oracle apps system administrator
--------- Terminating requests----------
SELECT Request_Id Reqst,
user_concurrent_program_name concurrent_program,
User_Name Requestor,
Oracle_Username Orcl,
Fcr.Logfile_Name LN,
Concurrent_Queue_Name Manager,
TO_CHAR(Actual_Start_Date, 'Mm-Dd-Yy Hh24:Mi') Started,
Single_Thread_Flag
FROM Fnd_Concurrent_Requests Fcr, Fnd_Concurrent_Programs_tl Fcp,
Fnd_Oracle_Userid O,
Fnd_Concurrent_Processes P,
Fnd_Concurrent_Processes P,
Fnd_Concurrent_Queues Q,
Fnd_User
Fnd_User
WHERE
Controlling_Manager = Concurrent_Process_ID
AND ( P.Concurrent_Queue_ID = Q.Concurrent_Queue_ID
AND P.Queue_Application_ID = Q.Application_ID )
AND O.Oracle_Id = Fcr.Oracle_Id
AND ( Fcr.Program_Application_Id = Fcp.Application_Id
AND Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id )
AND Requested_By = User_Id
AND Phase_Code = 'R' AND Status_Code = 'T'
ORDER BY Actual_Start_Date, Request_Id
------ Currently running requests ------------
SELECT Request_Id Reqst,
User_Name Requestor,
Oracle_Username Orcl,
Concurrent_Queue_Name Manager,
Concurrent_Program_Name Program,
user_concurrent_program_name conc_program,
user_concurrent_program_name conc_program,
TO_CHAR(Actual_Start_Date, 'Mm-Dd-Yy Hh24:Mi') Started,
Fcr.Logfile_Name LN,
Run_Alone_Flag, Single_Thread_Flag
FROM Fnd_Concurrent_Requests Fcr,
fnd_concurrent_programs_tl fcpt,
Fnd_Concurrent_Programs Fcp,
Fnd_Oracle_Userid O,
Fnd_Concurrent_Processes P,
Fnd_Concurrent_Queues Q, Fnd_User
WHERE
Controlling_Manager = Concurrent_Process_ID
AND ( P.Concurrent_Queue_ID = Q.Concurrent_Queue_ID
AND P.Queue_Application_ID = Q.Application_ID )
AND O.Oracle_Id = Fcr.Oracle_Id
AND ( Fcr.Program_Application_Id = Fcp.Application_Id
AND Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id )
AND (Fcpt.concurrent_program_id = Fcp.Concurrent_program_id)
AND Requested_By = User_Id
AND Phase_Code = 'R' AND Status_Code = 'R'
ORDER BY Actual_Start_Date, Request_Id
--- Summary of concurrent request execution since Date ---
SELECT L1.Meaning Request_Type,
L2.Meaning Status,
COUNT(Status_Code) NR
FROM Fnd_Concurrent_Requests R,
Fnd_Concurrent_Programs P,
Fnd_Lookups L1,
Fnd_Lookups L2
WHERE
L1.Lookup_Code = P.Execution_Method_Code
AND L1.Lookup_Type = 'CP_EXECUTION_METHOD_CODE'
AND (R.Program_Application_ID = P.Application_ID
AND R.Concurrent_Program_ID = P.Concurrent_Program_ID )
AND R.Concurrent_Program_ID = P.Concurrent_Program_ID )
AND (R.Status_Code = L2.Lookup_Code
AND L2.Lookup_Type = 'CP_STATUS_CODE' )
AND R.Phase_Code = 'C'
AND R.Actual_Completion_Date > SYSDATE - 5
GROUP BY
L1.Meaning, L2.Meaning
Errored programs on a day
SELECT fcp.user_concurrent_program_name
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp
WHERE status_code = 'E'
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND TRUNC(requested_start_date) = TRUNC(SYSDATE)
STATUS_CODE
A Waiting
B Resuming
C
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I
M No Manager
Q Standby
R
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting
Managers that are currently running a request
SELECT Concurrent_Queue_Name Manager,
Request_Id Request,
User_name,
Concurrent_Program_Name Program,
Run_Alone_Flag,
TO_CHAR(Actual_Start_Date, 'DD-MON-YY HH24:MI') Started
FROM Fnd_Concurrent_Queues Fcq,
Fnd_Concurrent_Requests Fcr,
Fnd_Concurrent_Programs Fcp,
Fnd_User Fu,
Fnd_Concurrent_Processes Fpro
WHERE
Phase_Code = 'R' AND
Fcr.Controlling_Manager = Concurrent_Process_Id AND (Fcq.Concurrent_Queue_Id = Fpro.Concurrent_Queue_Id AND
Fcq.Application_Id = Fpro.Queue_Application_Id ) AND (Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id AND Fcr.Program_Application_Id = Fcp.Application_Id ) AND
Fcr.Requested_By = User_Id;
Oracle Performance Tuning related Queries
SQL to identify the query which takes long time
Step1 : Run the first query , this will list all the programs that currently running in Application. Take the SID and use it in the second query.
SELECT
f.user_name
,a.request_id "Req Id"
,a.concurrent_program_id "Prg Id"
,a.RESPONSIBILITY_ID Responsibility
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vs.serial# "Serial#"
,vp.spid
,TO_CHAR(request_date,'DD-MON-YY hh24:mi:ss') request_date
,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||c2.user_concurrent_program_name "Program"
FROM ASYS.fnd_Concurrent_requests a
,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_user f
,v$session vs
,v$process vp
WHERE
a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND c2.concurrent_program_id = c.concurrent_program_id
AND c2.application_id = c.application_id
AND a.phase_code IN ('I','P','R','T')
AND a.requested_by = f.user_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND c2.LANGUAGE = 'US'
AND a.oracle_process_id = vp.spid
AND vs.paddr = vp.addr
ORDER BY 9
f.user_name
,a.request_id "Req Id"
,a.concurrent_program_id "Prg Id"
,a.RESPONSIBILITY_ID Responsibility
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vs.serial# "Serial#"
,vp.spid
,TO_CHAR(request_date,'DD-MON-YY hh24:mi:ss') request_date
,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||c2.user_concurrent_program_name "Program"
FROM ASYS.fnd_Concurrent_requests a
,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_user f
,v$session vs
,v$process vp
WHERE
a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND c2.concurrent_program_id = c.concurrent_program_id
AND c2.application_id = c.application_id
AND a.phase_code IN ('I','P','R','T')
AND a.requested_by = f.user_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND c2.LANGUAGE = 'US'
AND a.oracle_process_id = vp.spid
AND vs.paddr = vp.addr
ORDER BY 9
Step 2 : Get Sid from step1 and Keep on executing this query in SQL. This query will show the currently running SQL in the DB, as your concurrent is submitted and running. You can now find out the exact query ( select / insert / update ) which is actually taking time in your concurrent program.
SELECT sql_text FROM v$sqltext t,v$session s
WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE
WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE
Currently running sessions in database
Often it requires keeping track of the currently running sessions and the Sql executed in the sessions. So here is the query by which you can find out.
Get all the active sessions in the database
select username, sid, serial#, process, status, module, action, osuser
from v$session
where username is not null
and status = 'ACTIVE'
-- Get the sqls executed by the active sessions
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address,
LAST_CALL_ET,
osuser,
machine
from v$session
where status = 'ACTIVE'
rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(x.osuser||':'||x.machine);
dbms_output.put_line(substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
Get all the views that refer a particular table:
SELECT o.object_name, o.status, o.last_ddl_time
FROM SYS.dba_objects o
WHERE o.object_type = 'VIEW'
AND EXISTS (
SELECT 'x'
FROM SYS.dba_dependencies d
WHERE d.referenced_type = 'TABLE'
AND d.TYPE = 'VIEW'
AND d.owner = 'APPS'
AND d.NAME = o.object_name
Get all the tables with the column name:
SELECT
TABLE_NAME
FROM
all_tab_columns
WHERE
column_name = '<COLUMN_NAME>'
Get SQL help from the Data base itself:
If you need any syntax of a sql command or its details, you don’t need to search for it in google. what you just need to do is use the below sql command from the data base
In iSQL*Plus, click the Help button to display iSQL*Plus help. Or Use the below command line help system. In some DB system schema may not be available, then use sys.help instead.
select *
from system.help
where upper(topic)=upper('string')
Find from which responsibility a concurrent program can be run:
SELECT distinct
a.user_concurrent_program_name,
a.description,
request_group_name,
e.responsibility_name
FROM
fnd_concurrent_programs_tl a,
fnd_request_groups b,
fnd_request_group_units c,
fnd_responsibility d,
fnd_responsibility_tl e
WHERE
a.concurrent_program_id = c.request_unit_id
and b.request_group_id = c.request_group_id
and b.request_group_id = d.request_group_id
and d.responsibility_id = e.responsibility_id
and a.application_id = b.application_id
and b.application_id = c.application_id
and d.application_id = e.application_id
and a.concurrent_program_id = :p_conc_program_id
Find all running concurrent requests with their run times:
The below query is useful for analyzing concurrent program run times and identifying their performance
select request_id,
fcpt.user_concurrent_program_name,
completion_text,
actual_start_date,
actual_completion_date,
to_date((actual_completion_date - actual_start_date), 'HH:MM:SS') duration
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt
where fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_id = fcpt.concurrent_program_id
and fcr.actual_start_date > sysdate - 1
order by actual_completion_date - actual_start_date desc
Search all packages for a line of code:
SELECT *
FROM ALL_SOURCE
WHERE TYPE IN ('PACKAGE', 'PAGE BODY')
AND TEXT LIKE '%XXXX%'
The below table shows all the data base object information:
select * from dba_objects where object_name like '%HEADER%' and object_type = 'TABLE'
Sequal to get the concurrent program file name (procedure/pkg name) based on the concurrent program name
select fct.user_concurrent_program_name,
fcp.concurrent_program_name,
fe.execution_file_name,
fl.meaning execution_method
from fnd_concurrent_programs_tl fct,
fnd_concurrent_programs fcp,
fnd_executables fe,
fnd_lookups fl
where
upper(fct.user_concurrent_program_name) = upper('concurrent program')
and fct.concurrent_program_id = fcp.concurrent_program_id
and fe.executable_id = fcp.executable_id
and fl.lookup_code = fe.execution_method_code
and fl.lookup_type = 'CP_EXECUTION_OD_CODE'
fcp.concurrent_program_name,
fe.execution_file_name,
fl.meaning execution_method
from fnd_concurrent_programs_tl fct,
fnd_concurrent_programs fcp,
fnd_executables fe,
fnd_lookups fl
where
upper(fct.user_concurrent_program_name) = upper('concurrent program')
and fct.concurrent_program_id = fcp.concurrent_program_id
and fe.executable_id = fcp.executable_id
and fl.lookup_code = fe.execution_method_code
and fl.lookup_type = 'CP_EXECUTION_OD_CODE'
List of all active workflow users and their roles
select wu.name user_name,
wr.name role_name
from wf_users wu,
wf_user_roles wur,
wf_roles wr
where wu.name = wur.user_name
and wur.role_name = wr.name
and wu.status = 'ACTIVE'
and wr.status = 'ACTIVE'
and wr.orig_system = 'WF_LOCAL_ROLES'
order by wu.name,
wr.name
Concurrent requests raised in the last day
select request_id,
decode(parent_request_id, -1, 'None') as parent_request_id,
fcpt.user_concurrent_program_name,
decode( fcr.status_code,
'A', 'Waiting',
'B', 'Resuming',
'C', '',
'D', 'Cancelled',
'E', 'Error',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', '',
'M', 'No Manager',
'Q', 'Standby',
'R', '',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting') as status,
decode( fcr.phase_code,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running') as phase,
fu.user_name,
fr.responsibility_name,
completion_text,
argument_text,
request_date,
requested_start_date,
actual_start_date,
actual_completion_date,
fcp.concurrent_program_name,
fe.executable_name,
fe.execution_file_name
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_executables fe,
fnd_responsibility_vl fr,
fnd_user fu
where fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_id = fcpt.concurrent_program_id
and fcp.executable_id = fe.executable_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and fcr.request_date > sysdate - 1
--and fcr.concurrent_program_id = XXXX
order by request_id desc
How to find the Trace file generated for a concurrent program
You can use the following query to locate the trace file, it takes as input the ' Concurrent 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',
'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;
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'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;
The output would be 'directory'/'filename.trc'
eg: 'directory'/ora_3465_act.trc.
In the above directory, do 'ls -l' for '*3465*trc' and any latest file that you see with 3465 as a part of the file name would be the trace file.
eg: 'directory'/ora_3465_act.trc.
In the above directory, do 'ls -l' for '*3465*trc' and any latest file that you see with 3465 as a part of the file name would be the trace file.
Removing Table Lock
Login to database as SYS or SYSTEM user. Find out the SESSION_ID by running the following SELECT statement
SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name;
SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name;
Use this session id to find SERIAL# by using following SELECT statment
SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name)
SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name)
Use ALTER SYSTEM command to KILL SESSION and this will release the lock.
ALTER SYSTEM KILL SESSION 'SID,SERIALl#';
ALTER SYSTEM KILL SESSION 'SID,SERIALl#';
This script will report the SQL text of some of the locks currently being held in the database
select s.username username,
a.sid sid,
a.owner||'.'||a.object object,
s.lockwait,
t.sql_text SQL
from v$sqltext t,
v$session s,
v$access a
where t.address=s.sql_address
and t.hash_value=s.sql_hash_value
and s.sid = a.sid
and a.owner != 'SYS'
and upper(substr(a.object,1,2)) != 'V$'
Script to check Responsibilities assigned to a particular user or users assigned for particular responsibility or all users and their responsibilities:
SELECT fu.user_id,
fu.user_name,
fur.responsibility_id,
fr.responsibility_name
FROM fnd_user fu,
fr.responsibility_name
FROM fnd_user fu,
fnd_user_resp_groups fur,
fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
AND user_name like ‘OPERATIONS’ --- for all user or for particular user
AND fur.responsibility_application_id = 155 –- Check particular resp.
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
AND user_name like ‘OPERATIONS’ --- for all user or for particular user
AND fur.responsibility_application_id = 155 –- Check particular resp.
order by user_name
Script to find Oracle API's for any module:
select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'AR_%API%' –- Checking for AR Related APIs
order by
a.owner, a.name
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'AR_%API%' –- Checking for AR Related APIs
order by
a.owner, a.name
Query used for audit point of view i.e. when a profile is changed and by which user
SELECT t.user_profile_option_name,
profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date - v.last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = :p_value
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;
:p_value is (10001 -> site level, 10002 -> application level, 10003 -> responsibility level, 10004 – user level)
Check Current Applied Patch
SELECT patch_name, patch_type,
maint_pack_level,
creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date
Query used to view the patch level status of all modules
SELECT a.application_name,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level
FROM apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE a.application_id = b.application_id;
This query will shows concurrent program processing time
SELECT f.request_id ,
pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.descriptio
n||']',p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;
View all types of request Application wise
SELECT fa.application_short_name,
fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
output_file_type,
program_type,
printer_name,
minimum_width,
minimum_length,
concurrent_program_name,
concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY description
Script to display status of all the Concurrent Managers
Script to display status of all the Concurrent Managers
select distinct Concurrent_Process_Id CpId, PID Opid,
Os_Process_ID Osid,
Q.Concurrent_Queue_Name Manager,
P.process_status_code Status,
To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At
from Fnd_Concurrent_Processes P,
Fnd_Concurrent_Queues Q,
FND_V$Process
where Q.Application_Id = Queue_Application_ID
and Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID
and Spid = Os_Process_ID
and Process_Status_Code not in ('K','S')
order by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name
Print Oracle Apps versions
SELECT substr(a.application_short_name, 1, 5) code,
substr(t.application_name, 1, 50) application_name,
p.product_version version
FROM fnd_application a,
fnd_application_tl t,
fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.language = USERENV('LANG');
No comments:
Post a Comment