To find out the locked objects
If a table used by an user A is locked by User B then A needs to wait until B unlocks it. By issuing this query, User A can find which tables in his schema are locked and which session has locked it.
select a.sid,a.serial#,c.object_name
from V$session a,
V$locked_object b,
user_objects c
where a.sid=b.session_id
and b.object_id=c.object_id;
Output:
SID SERIAL# OBJECT_NAME
---- ------- ------------
7 36 emp
9 58 dept
2 rows selected.
Now you can release the lock:
SQL>alter system kill session '7,36';
System Altered.
SQL>alter system kill session '9,58';
System Altered.
To find the CPU consumption
select ss.sid,w.event,command,ss.value CPU ,se.username,se.program, wait_time, w.seq#, q.sql_text,command
from
v$sesstat ss, v$session se,v$session_wait w,v$process p, v$sqlarea q
where ss.statistic# in
(select statistic#
from v$statname
where name = 'CPU used by this session')
and se.sid=ss.sid
and ss.sid>6
and se.paddr=p.addr
and se.sql_address=q.address
order by ss.value desc,ss.sid
Script to show problem tablespaces
SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)< 10
OR next.max_next_extent > free.max_free)
order by pct_used desc
Oracle space monitoring scripts (grand total table space)
select
sum(tot.bytes/(1024*1024*1024))”Total size”,
sum(tot.bytes/(1024*1024*1024)-sum(nvl(fre.bytes,0))/(1024*1024*1024)) Used,
sum(sum(nvl(fre.bytes,0))/(1024*1024*1024)) Free,
sum((1-sum(nvl(fre.bytes,0))/tot.bytes)*100) Pct
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024*1024), tot.bytes
What's holding up the system?
Poorly written SQL is another big problem. Use the following SQL to determine the UNIX pid:
Select
p.pid, s.sid, s.serial#,s.status, s.machine,s.osuser, p.spid, t.sql_text
From
v$session s,
v$sqltext t,
v$process p
Where
s.sql_address = t.address and
s.paddr = p.addr and
s.sql_hash_value = t.hash_value and
s.sid > 7 and
s.audsid != userenv ('SESSIONID')
Order By s.status,s.sid, s.osuser, s.process, t.piece ;
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
Get current SQL from SGA
select sql_text
from V$session s , V$sqltext t
where s.sql_address=t.address
and sid=
order by piece;
You can find the SID from V$session.
Monitoring and Tuning the Shared Pool
select
sum(a.bytes)/(1024*1024) shared_pool_used,
max(b.value)/(1024*1024) shared_pool_size,
(max(b.value)/(1024*1024))-(sum(a.bytes)/(1024*1024)) shared_pool_avail,
(sum(a.bytes)/max(b.value))*100 shared_pool_pct
from v$sgastat a, v$parameter b
where a.name in (
'reserved stopper',
'table definiti',
'dictionary cache',
'library cache',
'sql area',
'PL/SQL DIANA',
'SEQ S.O.') and
b.name='shared_pool_size';
What SQL is running and who is running it?
select a.sid,a.serial#,a.username,b.sql_text
from v$session a,v$sqltext b
where a.username is not null
and a.status = 'ACTIVE'
and a.sql_address = b.address
order by 1,2,b.piece;
---
select decode(sum(decode(s.serial#,l.serial#,1,0)),0,'No','Yes') " ",
s.sid "Session ID",s.status "Status",
s.username "Username", RTRIM(s.osuser) "OS User",
b.spid "OS Process ID",s.machine "Machine Name",
s.program "Program",c.sql_text "SQL text"
from v$session s, v$session_longops l,v$process b,
(select address,sql_text from v$sqltext where piece=0) c
where (s.sid = l.sid(+)) and s.paddr=b.addr and s.sql_address = c.address
group by s.sid,s.status,s.username,s.osuser,s.machine,
s.program,b.spid, b.pid, c.sql_text order by s.status,s.sid
TO FIND THE SORTING DETAILS
SELECT a.sid,a.value,b.name from
V$SESSTAT a, V$STATNAME b
WHERE a.statistic#=b.statistic#
AND b.name LIKE 'sort%'
ORDER BY 1;
Long running SQL statements
SELECT s.rows_processed, s.loads, s.executions, s.buffer_gets,
s.disk_reads, t.sql_text,s.module, s.ACTION
FROM v$sql /*area*/ s,
v$sqltext t
WHERE s.address = t.address
AND ((buffer_gets > 10000000) or
(disk_reads > 1000000) or
(executions > 1000000))
ORDER BY ((s.disk_reads * 100) + s.buffer_gets) desc, t.address, t.piece
V$session_longops
SELECT * FROM (select
username,opname,sid,serial#,context,sofar,totalwork
,round(sofar/totalwork*100,2) "% Complete"
from v$session_longops)
WHERE "% Complete" != 100
Identify an object's locks in the database
Here are two simple scripts to identify an object's locks in the database. Whenever a user complains that there's a session locked, I use these scripts to find out if there are object locks.
# To find locks objects in the database
select c.Owner,c.Object_Name,c.Object_Type,
b.Sid,b.Serial#,b.Status,b.Osuser,b.Machine
from v$locked_object a ,v$session b,dba_objects c
where b.Sid = a.Session_Id
and a.Object_Id = c.Object_Id;
To find the locks and latches
select s.sid, s.serial#,
decode(s.process, null,
decode(substr(p.username,1,1), '?', upper(s.osuser), p.username),
decode( p.username, 'ORACUSR ', upper(s.osuser), s.process)
) process,
nvl(s.username, 'SYS ('||substr(p.username,1,4)||')') username,
decode(s.terminal, null, rtrim(p.terminal, chr(0)),
upper(s.terminal)) terminal,
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', decode(u.name, null,
'DICTIONARY OBJECT', u.name||'.'||o.name),
'TD', u.name||'.'||o.name,
'TM', u.name||'.'||o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE',
'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) object
from sys.v_$lock l, sys.v_$session s, sys.obj$ o, sys.user$ u,
sys.v_$process p
where s.paddr = p.addr(+)
and l.sid = s.sid
and l.id1 = o.obj#(+)
and o.owner# = u.user#(+)
and l.type <> 'MR'
UNION ALL /*** LATCH HOLDERS ***/
select s.sid, s.serial#, s.process, s.username, s.terminal,
'LATCH', 'X', 'NONE', h.name||' ADDR='||rawtohex(laddr)
from sys.v_$process p, sys.v_$session s, sys.v_$latchholder h
where h.pid = p.pid
and p.addr = s.paddr
UNION ALL /*** LATCH WAITERS ***/
select s.sid, s.serial#, s.process, s.username, s.terminal,
'LATCH', 'NONE', 'X', name||' LATCH='||p.latchwait
from sys.v_$session s, sys.v_$process p, sys.v_$latch l
where latchwait is not null
and p.addr = s.paddr
and p.latchwait = l.addr
To clear the log files:
1) move the alert log file and recreate one dummy alert log file
using touch command
path $ORACLE_HOME/admin/bdump
2) move network log file and recreate one dummy file
location $ORACLE_HOME/network/admin
3) move the Apache log files (access_log and error_log) and recreate one dummy file
Location $iAS_ORACLE_HOME/Apache/Apache/logs
4) move the Jserv log file and create one dummy file
Location $iAS_ORACLE_HOME/Apache/Jserv/logs
=======================================================================
Move a table from one tablespace to another
There are many ways to move a table from one tablespace to another. For example, you can create a duplicate table with dup_tab as select * from original_tab; drop the original table and rename the duplicate table as the original one.
The second option is exp table, drop it from the database and import it back. The third option (which is the one I am most interested in) is as follows.
Suppose you have a dept table in owner scott in the system tablespace and you want to move in Test tablespace.
connect as sys
SQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT SYSTEM
Elapsed: 00:00:00.50
You want to move DEPT table from system to say test tablespace.
SQL :> connect scott/tiger
Connected.
SQL :> alter table DEPT move tablespace TEST;
Table altered.
Elapsed: 00:00:00.71
SQL :> connect
Enter user-name: sys
Enter password:
Connected.
SQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT TEST
TKPROF Command
ALTER SESSION SET sql_trace = TRUE
ALTER SYSTEM SET TIMED_STATISTICS = TRUE
Session level
ALTER SESSION SET sql_trace = TRUE (or) ALTER SYSTEM SET sql_trace =
TRUE
EXECUTE SYS.dbms_system.set_sql_trace_in_session (
TKPROF
To find the location of Dump files
select c.value || '/' || instance || '_ora_' ||
ltrim(to_char(a.spid,'fm99999')) || '.trc'
from v$process a, v$session b, v$parameter c, v$thread c
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
TO find the patch set level
Please follow the note 120638.1
select patch_level
from fnd_product_installations
where application_id = 200;
To compile the procedure
Alter PROCEDURE JA_IN_BULK_PO_QUOTATION_TAXES compile
To compile the form
F60gen userid=apps/metroapps@dev module=
output_file=
module_type=form batch=no compile_all=special
Compiling DFFs
cd $JA_TOP/4239736
fdfcmp apps/metroapps@dev 0 Y R "INV" "JAF23A_2"
Move all log files to the patch log directory
Find . –name “XXXX” –type f –mtime +90 –exec rm {} \;
Find . –name “XXXX” –type f –mtime +90 –print | sargs cp –R /vasu
find . -name "*.log" | grep -v "/log/" | xargs -i mv {} $JA_TOP/$APPLLOG/4239736
cd %AU_TOP%\resource
ifcmp60 module="JAINTAX.pll" userid=apps\%1 output_file="%AU_TOP%\resource\JAINTAX.plx" module_type=library batch=yes
@REM Forms
echo "Generating forms."
cd %AU_TOP%\forms\US
ifcmp60 module="JAIN57F4.fmb" userid=apps/%1 output_file="%JA_TOP%\forms\US\JAIN57F4.fmx" batch=yes
ifcmp60 module="JAIRGMST.fmb" userid=apps/%1 output_file="%JA_TOP%\forms\US\JAIRGMST.fmx" batch=yes
How to find versions
*************************************************************
This article is being delivered in Draft form and may contain
errors. Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
*************************************************************
PURPOSE
-------
The purpose of this note is to bring together methods to get version of
programs, executables, forms, reports, database objects and other files
involved in Oracle Applications v. 11.x.
SCOPE & APPLICATION
-------------------
All audience. Version of objects are often needed by support.
CONTENTS
--------
1. Oracle Applications
2. Forms
3. Reports
4. SQL or PL/SQL scripts
5. Executables
6. Other files
7. RDMBS
8. Database objects
9. Operating System
select release_name from fnd_product_groups
select organization_id org_id, name from hr_operating_units;]
Get the JDK version
[JDK_TOP]/bin/java -version
Example run of adjkey
-------------------------------
cd $APPL_TOP/admin
$ adjkey -initialize
Regenerate Application Jar Files
----------------------------------
Run ADADMIN to Regenerate (sign) the JAR files on each middle tier
1. Launch ADADMIN (Ensure you are APPLMGR with permissions to write to adadmin.log)
2. Choose option number 2 to Maintain Files, then 10 to regenerate JAR Files making sure to select FORCE = Y which will resign every JAR file using the new digital certificate that you just copied over from your original instance.
1. ORACLE APPLICATIONS
a. from any forms you can get Oracle Applications version by this menu option :
Main Menu => Help => About Oracle Applications ...
A pop-up window displays, among other things, version of :
Oracle Applications
current used module
Oracle Forms
RDBMS
current open form
b. you can also run the following command :
sqlplus applsys/
select release_name from fnd_product_groups;
select * from fnd_product_installations;
2. FORMS
a. if the form is displayed, see §1 above to get current open form version
b. in case of the form doesn't appear you must :
- retreive the form name from an other environment without the problem
(i.d. NLS, test or production, etc.) or from WEB IV, Metalink, ARU...
- go to
should find the corresponding file with .fmx extension
- see §6 below to get the file version
3. REPORTS
- you need first to note the report name on top of it's log file
- go to
you should find the corresponding file with .rdf extension
- see §6 below to get the file version
4. SQL OR PL/SQL SCRIPTS
- go to
You should find the corresponding file with .sql, .pls, .pkh, or .pkb
extension
- see §6 below to get the file version
5. EXECUTABLES
- binary or executable names have often no extension on unix systems
or present .exe or .dll extension on MS-Windows.
- most of them are located under
- there are many ways to get the version of an executable,
you can try the following methods :
a. if an interface is displayed, go to the menu => Help => About ...
e.g. Oracle Applications, internet browsers, tools (Oracle Forms,
Oracle Reports, Enterprise Manager, SQL*Plus)
'Help => About Plug-ins' give JInitiator version
b. run the file without parameter
e.g. f45gen, f60gen (for Oracle Forms)
r25convm, rwcon60 (for Oracle Reports)
sqlplus (for SQL*Plus)
tnsping (TNS Ping Utility)
jre (Java Runtime Loader)
c. see properties of the file with MS-Windows Explorer
e.g. *.exe, *.dll files
d. find 'Header' string, see §6 to proceed
e.g. ad utilities (adpatch, adrelink, etc.), fnd executables,
binaries under
e. run specific command
e.g. Appletviewer :
java -version
Oracle Workflow in Oracle Applications :
sqlplus apps/
@$FND_TOP/sql/wfver.sql
or
select TEXT from WF_RESOURCES where NAME='WF_VERSION';
f. launch Oracle Installer
Several Oracle products (like RDBMS, Tools) need orainst to be installed,
below is the way to launch it and get version of products:
- login with Oracle account
- run Oracle Installer by :
. $ORACLE_HOME/orainst/orainst
or under MS-Windows :
$ORACLE_HOME\bin\orainst.exe
- answer by default to reach 'Software Asset Manager' screen
- right column shows you installed products and versions
Same informations are in these files :
$ORACLE_HOME/orainst/unix.rgs (Unix)
$ORACLE_HOME\orainst\nt.rgs , windows.rgs (Win NT, MS-Windows)
6. OTHER FILES
- other files could be:
driver files (*.drv)
object description files (*.odf)
data files (*.dat)
library and object files (*.a, *.o)
Oracle Forms libraries (*.pll, *.plx)
Oracle Forms menu files (*.mmb, *.mmx)
form source files (*.fmb)
jar file (*.jar)
java class file (*.class)
html, xlm files (*.htm, *.xlm)
- go to the corresponding directory
- execute one of the following commands to get the version of the file
on all platforms (beginning with Oracle Applications v. 11.x) :
adident Header
on Unix :
strings -a
on Windows (DOS box) :
find "Header"
7. RDMBS
a. See §1.a to get easily the version of your Oracle Server installed
b. You can also execute sqlplus, it displays SQL*Plus and RDBMS version
e.g. Oracle8 Enterprise Edition Release 8.0.6.1.
c. see $5.f if you prefer to use Oracle Installer which displays also
version of several installed products.
8. DATABASE OBJECTS
a. run this sql statement to get package version :
select text from user_source where name='&package_name'
and text like '%$Header%';
prompt asks you the package name, in return it gives you two lines
corresponding to specifications and body creation files
You can also get pls version on database by running:
select name , text
from dba_source
where text like '%
and line < 10;
b. views
Sometimes version information is available in view definition.
Try the following sql statement :
col TEXT for a40 head "TEXT"
select VIEW_NAME, TEXT
from USER_VIEWS
where VIEW_NAME = '&VIEW_NAME';
c. workflow
Run wfver.sql (see §5.e) to get version of workflow packages and views.
9. OPERATING SYSTEM
a. for most Unix platforms run command :
uname -a
b. for MS-WINDOWS 95/98/2000
Start => Parameters => Control Panel => System
c. for WIN/NT, execute command :
winver
or menu :
Start => Programs => Admin Tools => WIN NT Diagnostic
RELATED DOCUMENTS
-----------------
Note 106767.1 How To Determine The Version Of An Applications Form In Rele
ROLL BACK SEGMENTS
dba_rollback_segs
v$transaction
v$rollname
v$undostat
alter index gl_interface_n1 coalesce;
alter index gl_interface_n1 rebuild nologging;
Monitoring Pending Requests in the Concurrent Managers
• FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES
Select *
From Fnd_Concurrent_Requests R, Fnd_Lookups L
Where R.Status_Code = L.Lookup_Code
And L.Lookup_Type = 'CP_STATUS_CODE'
And Phase_Code = 'C'
-- And Actual_Completion_Date - &DaysPrior
and meaning in ('Error','Warning')
-- and request_date = '6/13/2006'
--Group BY Meaning;
Tables that are updated when Oracle Applications Concurrent Program is started
FND_CONCURRENT_REQUESTS This table contains a complete history of
all concurrent requests.
FND_RUN_REQUESTS When a user submits a report set, this table
stores information about the reports in the
report set and the parameter values for each
report.
FND_CONC_REQUEST_ARGUMENTS This table records arguments passed by the
concurrent manager to each program it starts
running.
FND_DUAL This table records when requests do not
update database tables.
FND_CONCURRENT_PROCESSES This table records information about Oracle
Applications and operating system processes.
FND_CONC_STAT_LIST This table collects runtime performance
statistics for concurrent requests.
FND_CONC_STAT_SUMMARY This table contains the concurrent program
performance statistics generated by the
Purge.
The lookup for the output would be:
PHASE CODE:
Value Meaning
I Inactive
P Pending
R Running
C Completed
STATUS CODE:
Value Meaning
U Disabled
W Paused
X Terminated
Z Waiting
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
A Waiting
B Resuming
C Normal
For long running requests.
Log onto SQLPLUS as user APPS. Enter the following command:
update fnd_concurrent_requests set phase_code=‘C’, status_code=’D’ where request_id=reqid;
commit;
su - applprod
cd $FND_TOP
cd sql
afcmstat.sql Displays all the defined managers, their maximum capacity, pids, and their status.
afimchk.sql Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.
afcmcreq.sql Displays the concurrent manager and the name of its log file that processed a request.
afrqwait.sql Displays the requests that are pending, held, and scheduled.
afrqstat.sql Displays of summary of concurrent request execution time and status since a particular date.
afqpmrid.sql Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.
afimlock.sql Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with another oracle process.
Occasionally, you may find that requests are stacking up in the concurrent managers with a status of "pending". This can be caused by any of these conditions:
1. The concurrent managers were brought down will a request was running.
2. The database was shutdown before shutting down the concurrent managers.
3. There is a shortage of RAM memory or CPU resources.
When you get a backlog of pending requests, you can first allocate more processes to the manager that is having the problem in order to allow most of the requests to process, and then make a list of the requests that will not complete so they can be resubmitted, and cancel them.
To allocate more processes to a manager, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Manager -> Define. Increase the number in the Processes column. Also, you may not need all the concurrent managers that Oracle supplies with an Oracle Applications install, so you can save resources by identifying the unneeded managers and disabling them.
However, you can still have problems. If the request remains in a phase of RUNNING and a status of TERMINATING after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate, and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:
update fnd_concurrent_requests
set status_code='X', phase_code='C'
where status_code='T';
conc_stat.sql
set echo off
set feedback off
set linesize 97
set verify off
col request_id format 9999999999 heading "Request ID"
col exec_time format 999999999 heading "Exec Time|(Minutes)"
col start_date format a10 heading "Start Date"
col conc_prog format a20 heading "Conc Program Name"
col user_conc_prog format a40 trunc heading "User Program Name"
spool long_running_cr.lst
SELECT
fcr.request_id request_id,
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) exec_time,
fcr.actual_start_date start_date,
fcp.concurrent_program_name conc_prog,
fcpt.user_concurrent_program_name user_conc_prog
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_requests fcr
WHERE
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)
and
fcr.concurrent_program_id = fcp.concurrent_program_id
and
fcr.program_application_id = fcp.application_id
and
fcr.concurrent_program_id = fcpt.concurrent_program_id
and
fcr.program_application_id = fcpt.application_id
and
fcpt.language = USERENV('Lang')
ORDER BY
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) desc;
spool off
Note that this script prompts you for the number of minutes. The output from this query with a value of 60 produced the following output on my database. Here we can see important details about currently-running requests, including the request ID, the execution time, the user who submitted the program and the name of the program.
Enter value for min: 60
No comments:
Post a Comment