ORA-01400

Autoconfig errors on adgentns.pl ORA-01400: cannot insert NULL into FND_APPS_SYSTEM.CSI_NUMBER (Doc ID 1209823.1)

APPLIES TO:

Oracle Applications DBA - Version 12.0.0 to 12.1.3 [Release 12 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 25-Jun-2013***

SYMPTOMS

On application Release 12 When attempting to run autoconfig on APPS Tier, the following error occurs:

ERROR: [CVM Error Report]
The following report lists errors encountered during CVM Phase
/.../apps/apps_st/appl/ad/12.0.0/bin/adgentns.pl 2
No of scripts failed in CVM phase: 1 AutoConfig is exiting with status 1
ERROR in NetServiceHandler.log:

Registering Middle Tier Node
[ Input Information ]
......
Tools Local : null
Web Local : null
StackTrace:
java.sql.SQLException:
ORA-01400: cannot insert NULL into ("APPLSYS"."FND_APPS_SYSTEM"."CSI_NUMBER")
ORA-06512: at "APPS.FND_APP_SYSTEM", line 39
ORA-06512: at "APPS.FND_NET_SERVICES", line 1720
ORA-06512: at line 1


CAUSE

A null value for parameter s_systemcsi in APPS Context XML File This makes autoconfig script to
insert NULL value to ("APPLSYS"."FND_APPS_SYSTEM"."CSI_NUMBER") generating the error message :
ORA-01400: cannot insert NULL into ("APPLSYS"."FND_APPS_SYSTEM"."CSI_NUMBER")

SOLUTION

To implement the solution, please execute the following steps:

1. Source the APPS Tier environment file $APPL_TOP/APPS_.env.

2. Backup the APPS Tier Context File

3. Edit APPS Tier tier context XML file,

Replace:

<AFSYSCSI oa_var="s_systemcsi"/>
With:

<AFSYSCSI oa_var="s_systemcsi">0</AFSYSCSI>
4. Save the changes.

5. Run autoconfig to re-test the issue.

AC-50480

Autoconfig fails: AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora. (Doc ID 1500361.1)

APPLIES TO
Oracle Applications Technology Stack - Version 12.0.4 to 12.0.4 [Release 12.0]
Information in this document applies to any platform.

SYMPTOMS
Autoconfig fails with the following error: AC-50480: Internal error occurred:
java.lang.Exception: Error while generating listener.ora.
------details adconfig.log -----
[CVM Error Report]
The following report lists errors encountered during CVM Phase
 
 /apps/***_***/apps_home/appl_top/ad/12.0.0/bin/adgentns.pl 2

No of scripts failed in CVM phase: 1
AutoConfig is exiting with status 1
-----Further details -------------------------
##########################################################################
  Generate Tns Names
##########################################################################
Logfile: /apps/***_***/apps_home/inst_top/apps/ASGPERF_ap*****/admin/log/08131223/NetServiceHandler.log
Classpath : /apps/***_***/apps_home/comn_top/java/lib/appsborg2.zip:/apps/***_***/apps_home/comn_top/java/classes

Updating s_tnsmode to 'generateTNS'
UpdateContext exited with status: 0
AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.
Error generating tnsnames.ora from the database, temporary tnsnames.ora will be generated using templates
Instantiating Tools tnsnames.ora
Tools tnsnames.ora instantiated
Web tnsnames.ora instantiated

adgentns.pl exiting with status 2
ERRORCODE = 2 ERRORCODE_END
.end std out.

.end err out.
  Result : FAILED

FileName
----------------
adconfig.log (157.91 KB)


CAUSE

The file : /orabase/asg/product/db/10.2.0/network/admin/ASGPERF_apl08956pmjudbt/sqlnet_ifile.ora

is a copy of sqlnet.ora and therefore the IFILE is pointing to itself.

 SOLUTION

Please make the IFILE /orabase/asg/product/db/10.2.0/network/admin/ASGPERF_ap*****/sqlnet_ifile.ora into a blank file.

Then try to start the listener.

The listener should now start.
Also when Autoconfig is next run, the IFILE which is not built through Autoconfig
should not be reconfigured, it should remain blank and therefore running Autoconfig would not affect a change.

ORA-01795

ORA-01795: maximum number of expressions in a list is 1000 tips



Cause: More than 254 columns or expressions were specified in a list.


Action: Remove some of the expressions from the list.

How to change location of oraInventory in R12.2.4

Find the current location of the Oracle Inventory (default $ORACLE_BASE/oraInventory)


Open the oraInst.loc file in /etc/ and check the value of inventory_loc

[root@sujeet ]$ cat /etc/oraInst.loc
inventory_loc=/u01/app/oraInventory
inst_group=oinstall
[root@sujeet ]$ Copy the oraInventory directory to the destination directory
[root@sujeet ]$ cp -Rp /u01/app/oraInventory /oracle/oraInventory
Edit the oraInst.loc file to point to the new location
For example:
[root@sujeet ]$ vi /etc/oraInst.loc
inventory_loc=/oracle/oraInventory inst_group=dba
Note: The oraInst.loc file is simply a pointer to the location of the Oracle Inventory (oraInventory)

What is oraInventory ?
oraInventory is repository (directory) which store/records oracle software products & their oracle_homes location on a machine. This Inventory now a days in XML format and called as XML Inventory where as in past it used to be in binary format & called as binary Inventory.
There are basically two kind of Inventory Global Inventory (also called as Central Inventory) and Local Inventory also called as Oracle Home Inventory.

Global Inventory ?
Global Inventory holds information about Oracle Products on a Machine. These products can be various oracle components like database, oracle application server, collaboration suite, soa suite, forms & reports or discoverer server . This global Inventory location will be determined by fileoraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris). If you want to see list of oracle products on machine check for file inventory.xml underContentsXML in oraInventory.

ERROR: Script failed, exit code 255

adpreclone error returning status 255 ERROR: Script failed, exit code 255


Issue:
I had error when running adpreclone appsTier for ebs run file system on r12.2.4
ERROR:
returning status 255 ERROR: Script failed, exit code 255
SOLUTION:
check the inventory on oraInst.loc.
test:
opatch lsinventory

RC-50208 Error results After Cloning R12.2.3

RC-50208 Error results After Cloning R12.2.3 (Doc ID 1904408.1)


APPLIES TO:

Oracle Applications Manager - Version 12.2.3


SYMPTOMS

On Oracle Applications 12.2.3 Cloning the Applications tier errors as follows:

RC-50208: Exception  in method TimedProcessProcess.run
Raised by oracle.apps.ad.util.TimedProcess$TimedProcessProcess: java.io.IOException:
Cannot run program "/engn001/erpptc/fs2/FMW_Home/webtier/perl/bin/perl": error=2, No such file or directory script returned:

The issue can be reproduced at will with the following steps:
1. Run clone using RapidClone.
3. The error above occurs.


CAUSE

The FMW_Home.jar file isn't generated successfully.

One can check the FMW_Home.jar file size about source and target system.
This file should be in the $COMMON_TOP/clone/FMW directory.


SOLUTION

 To implement the solution, please execute the following steps:

1. Execute the pre-clone command again.

2. Copy FMW_Home.jar file from the source to the target.

adcfgclone.pl on dbTier or appsTier (R12.2.4)

Database Tier:

 $ORACLE_HOME/appsutils/clone/binperl adcfgclone.pl dbTier pwd=apps
This will use the templates and driver files those were created while running adpreclone.pl on source system and has been copied to target system.

Following scripts are run by adcfgclone.pl dbTier for configuring techstack

adchkutl.sh — This will check the system for ld, ar, cc, and make versions.
adclonectx.pl — This will clone the context file. This will ceate a new context file as per the details of this instance.
runInstallConfigDriver — located in $Oracle_Home/appsutil/driver/instconf.drv
Relinking $Oracle_Home/appsutil/install/adlnkoh.sh — This will relink ORACLE_HOME

For data on database side, following scripts are run
Driver file $Oracle_Home/appsutil/clone/context/data/driver/data.drv
Create database adcrdb.zip
Autoconfig is run
Control file creation adcrdbclone.sql

Application Tier:


COMMON_TOP/clone/bin/perl adcfgclone.pl appsTier pwd=apps
Following scripts are run by adcfgclone.pl
Creates context file for target adclonectx.pl

Run driver files
$ORACLE_HOME/appsutil/driver/instconf.drv
$IAS_ORACLE_HOME/appsutil/driver/instconf.drv

Relinking of Oracle Home$ORACLE_HOME/bin/adlnk806.sh$IAS_ORACLE_HOME/bin/adlnkiAS.sh
At the end it will run the driver file $COMMON_TOP/clone/appl/driver/appl.drv and then runs autoconfig.

adpreclone.pl on dbTier and AppsTier (R12.2.4)

adpreclone.pl - This is the preparation phase, will collects information about the source system, creates a cloning stage area, and generates templates and drivers. All of these are to reconfigure the instance on a Target machine.

Preclone will do the following:

Convert Symbolic links
All symbolic links pointing to a static path will be converted into relative paths

Create templates
Any files under the $ORACLE_HOME that contain system specific information, will be replicated and converted into a template. These templates are placed into the $ORACLE_HOME/appsutil/template directory.

Create driver(s)
A driver file, relating to these new templates is created called instconf.drv. This contains a list of all the templates and their locations, and the destination configuration files that these templates will create.
This driver file is called instconf.drv and is placed into directory
$ORACLE_HOME/appsutil/driver

Create Stage area
A clone stage is created containing the required java code and scripts to reconfigure the instace on the Target machine

Rapid Clone stage area:
dbTier : $ORACLE_HOME/appsutil/clone
appsTier(s) - $COMMON_TOP/clone

The stage area(s) consist of the following directories:-
 jre used to run the java code on the Target machine.
 bin contains the RapidClone scripts that can be run on the Target machine:-

  •    adclone.pl is the main cloning script
  •    adcfgclone.pl is used to configure the Target system, this calls adclone.pl
  •    adclonectx.pl is used to clone a Source XML file manually
  •    adaddnode.pl is used to add a new node to the Patch History tables
  •    adchkutl.sh checks for existence of require O/S utils, cc, make, ar and ld

 jlib contains all the Rapid Clone java code, jdbc libraries etc
 context contains templates used for a Target XML file
 data (Database Tier only) contains the driver file, and templates used to generate the control file SQL script
 adcrdb.zip contains the template and list of datafiles on the Source
 addbhomsrc.xml contains information on the datafile mount points of the Source
 appl (Applications Tier only) this is used when merging appltops, i.e Multi-node to Single node cloning

Executing adpreclone.pl will create a log file:-
Rapid Clone:
dbTier : $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/StageDBTier_xxxxxx.log
appsTier : $APPL_TOP/admin/$CONTEXT_NAME/log/StageAppsTier_xxxxxx.log

Once this adpreclone.pl step has been completed successfully, all the java .class files under the following directories should be identical to those under $JAVA_TOP/oracle :

RDBMS $ORACLE_HOME/appsutil/java/oracle
RDBMS $ORACLE_HOME/appsutil/clone/jlib/java/oracle
$COMMON_TOP/clone/jlib/java/oracle

RC-50208: Exception in method TimedProcessProcess.run

I am facing error when running adcfgclone appsTier for EBS run file system on R12.2.4
ERROR:
fmw_home/webtier not created.
RC-50208: Exception  in method TimedProcessProcess.run
Raised by oracle.apps.ad.util.TimedProcess$TimedProcessProcess: java.io.IOException: Cannot run program "/u02/apps/fs1/FMW_Home/              webtier/perl/bin/perl": error=2, No such file or directory
script returned:
****************************************************



SOLUTION:
The FMW_Home.jar file isn’t generated successfully.
One can check the FMW_Home.jar file size about source and target system. This file should be in the $COMMON_TOP/clone/FMW directory.
1. Execute the pre-clone command again on source system.
2. Copy FMW_Home.jar file from the source to the target.
3. Re-run adcfgclone.


Kernel panic – not syncing: Attempted to kill init!



In most cases this cause by a faulty SELinux setting most likely on system file label.
This can happened if you just upgraded your Linux box or played with SELinux while trying to remove or disable it.
Before you can actually try to fix the issue with SELiunx that causing the system to hang at boot, you need to get your system booting up again.
Here’s how to do it.
In this example I will be using CentOS 5.9 but these steps should apply to any recent CentOS/RHEL.

STEP 01:

Restart the server an as soon as you see the boot screen as in the picture below (or similar depend on your distribution), press Esc button on the server keyboard. (you can actually press any key but we want to be safe).

Doing so will take you into Boot option menu as in the picture below.

STEP-2
Using the ↑and ↓keys choose the first boot menu in this case CenOS (2.6.18.348.18.1.el5)
 and press A in the server keyboard to get the next screen as below.


STEP-3
In this screen menu append this to the end of the line selinux=0 enforcing=0.
So the whole line will look like this…
grub append> ro root=/dev/VolGroup00/LogVo100 selinux=0 enforcing=0

Your line might look other then the example but the idea is to append the arguments to the end of the line.

Note that we disable both, SELinux and enforcing, in most cases you will only need one of them however by using both arguments
we make sure the system boot even if SELinux is disabled but left enforcing or the targeted policy is missing or corrupted.


Once you done appending the arguments to the Kernel boot line, hit Enter and everything went well the system will
continue to boot and you should get the terminal login screen as below.

Once you have managed to get into the terminal, is now time to look at the issue that may cause the kernel panic.
Bare in mind that until now you have fixed noting but disabling the SELinux from causing the system to hang at boot. You now have to look into the cause of it.

Make sure you are logged in as root and run this command

# getenforce
Enforcing
The getenforce command returns Permissive when SELinux is enabled, but SELinux policy rules are not enforced or returns Disabled if SELinux is disabled.
Running The sestatus command returns the SELinux status and the policy being used

# sestatus
SELinux status:                 enabled
SELinuxfs mount:                /selinux
Current mode:                   enforcing
Mode from config file:          enforcing
Policy version:                 23
Policy from config file:        targeted
In our case, the SELinux is enabled and running in enforcing mode using targeted policy.
As we already figure it up, in most cases kernel hang cause by a faulty SELinux setting most likely on system file label, you should make sure that system label are corrected.
The easiest way to do so is by relabeling the whole system using the SELinux its self, but in order for it to be able to do, you have to make sure that SELinux is running and in permissive mode.
First use the following command to confirm that the SELinux packages are installed.

# rpm -qa | grep selinux
libselinux-python-1.33.4-5.7.el5
libselinux-utils-1.33.4-5.7.el5
libselinux-devel-1.33.4-5.7.el5
selinux-policy-targeted-2.4.6-338.el5
libselinux-1.33.4-5.7.el5
selinux-policy-2.4.6-338.el5
Assuming the following packages are installed: selinux-policy-targeted, selinux-policy, libselinux, libselinux-python, libselinux-utils, policycoreutils
If one of the above is missing or corrupted install it or reinstall it using Yum command. if you want to be safe just reinstall using this command

# yum -y install libselinux libselinux-python libselinux-utils policycoreutils selinux-policy selinux-policy-targeted
Once you confirmed that all required packages are installed, run this command and make sure that SELinux is configured to run in permissive mode

# cat /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#       enforcing - SELinux security policy is enforced.
#       permissive - SELinux prints warnings instead of enforcing.
#       disabled - No SELinux policy is loaded.
SELINUX=permissive
# SELINUXTYPE= can take one of these two values:
#       targeted - Targeted processes are protected,
#       mls - Multi Level Security protection.
SELINUXTYPE=targeted
VERY IMPORTANT:
If in the above the line show SELINUX=enforcing or SELINUX=disabled, edit the file using your favorite editor make sure sure it say SELINUX=permissive as above and then reboot the system before you do the next step.

Run the below command and make sure SELinux is runing and in permissive mode.

# setenforce 0
# getinforce
permissive
With the above done, run this command to create a file on root system named .autorelabel and force the system to reboot.

# touch /.autorelabel
# reboot
During the next boot, if SELinux find the file ./autorelable it will relabel all system files and then delete the autorelable file.

*** Warning -- SELinux targeted policy relabel is required.
*** Relabeling could take a very long time, depending on file
*** system size and speed of hard drives.
****
Once the system is up and running again, you should run this command to make sure that SELinux did not deny actions during the last boot.

# grep "SELinux is preventing" /var/log/messages
If SELinux did not deny actions during the last boot, this command does not return any output and it is now safe to go ahead and enforce SELinux.
Edit the file /etc/selinux/config and this time append SELINUX=enforcing.

Reboot your system and make sure SELinux is running in enforcing mode

# getenforce
Enforcing

Concurrent request scripts

Overview of Concurrent Processing

In Oracle Applications, concurrent processing simultaneously executes programs running in the background with online operations. As System Administrator, you can manage when programs are run and how many operating system processes Oracle Applications devotes to running programs in the background.

Concurrent Requests, Programs, and Processes

When a user runs a report, a request to run the report is generated. The command to run the report is a concurrent request. The program that generates the report is a concurrent program. Concurrent programs are started by a concurrent manager.



Concurrent Managers start concurrent programs

Every time your users request a concurrent program to be run, their request is inserted into a database table, and is uniquely identified by a request ID. Concurrent managers read requests from this table.
Part of a manager's definition is how many operating system processes it can devote to running requests. This number is referred to as the manager's number of target processes.

Running concurrent programs

A concurrent program actually starts running based on:

  • When it is scheduled to start
  • Whether it is placed on hold,
  • Whether it is incompatible (cannot run) with other programs
  • Its request priority

Concurrent Request Priorities

The priority of a concurrent request is determined by application username, and is set by the System Administrator using the Concurrent:Priority user profile option.
The first available concurrent manager compares the request's priority to other requests it is eligible to process, and runs the request with the highest priority.
When choosing between requests of equal priority, the concurrent manager runs the oldest request first.

Parent requests and Child requests

Often, several programs may be grouped together, as in a request set. Submitting the request set as a whole generates a request ID, and as each member of the set is submitted it receives its own request ID. The set's request ID identifies the Parent request, and each of the individual programs' request ID identifies a Child request.

 locks in concurrent jobs 

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

 concurrent programs running currently with Details of Processed time-- and Start Date

 SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
 a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
 (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM   apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.FND_CONCURRENT_PROGRAMS_TL c,apps.fnd_user d
WHERE  a.concurrent_program_id=b.concurrent_program_id AND b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by=d.user_id AND status_code='R' order by Process_time desc;
   
last run of a Concurrent Program along with Processed time
Useful to find the Details of Concurrent programs which run daily and comparison purpose

SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
            round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,
            a.request_id,a.parent_request_id,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS'),To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS'),
  To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS'), (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
            (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
            d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM   apps.fnd_concurrent_requests a,
            apps.fnd_concurrent_programs b ,
            apps.FND_CONCURRENT_PROGRAMS_TL c,
            apps.fnd_user d
WHERE       a.concurrent_program_id= b.concurrent_program_id AND
            b.concurrent_program_id=c.concurrent_program_id AND
            a.requested_by =d.user_id AND
--          trunc(a.actual_completion_date) = '24-AUG-2005'
c.USER_CONCURRENT_PROGRAM_NAME='Incentive Compensation Analytics - ODI' --  and argument_text like  '%, , , , ,%';
--          and status_code!='C'

For Checking the last run of concurrent Program.
- Use below query to check all the concurrent request running which may refer given package
-- This is very useful check before compiling any package on given instance.
-- The query can be modified as per requirement.
-- Remove FND_CONCURRENT_REQUESTS table and joins to check all program dependent on given package.

SELECT
 FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required
--AND referenced_owner = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&Package_name')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND fcr.phase_code NOT IN ( 'C','P');

-- Query 5:The following query will display the time taken to execute the concurrent Programs
--for a particular user with the latest concurrent programs sorted in least time taken
-- to complete the request.

 SELECT
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      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,
      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_start_date desc;

--Query 6: By using the below Query we can get sid,serial#,spid of the concurrent Request..
   
    SELECT a.request_id, d.sid, d.serial# , c.SPID
    FROM apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_processes b,
    v$process c,
    v$session d
    WHERE a.controlling_manager = b.concurrent_process_id
    AND c.pid = b.oracle_process_id
    AND b.session_id=d.audsid
    AND a.request_id = &Request_ID
    AND a.phase_code = 'R';

By using below Concurrent Manager and Program rules...
Gives Detail of the Concurrent_queue_name and User_concurrent_program_name

SELECT b.concurrent_queue_name, c.user_concurrent_program_name
FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
WHERE a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(INCLUDE_FLAG, 'I', 1, 2), type_code;
   
 Gives Details of Running Concurrent jobs

SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
      round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
    a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
      (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
      (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
      d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM     apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_programs b ,
    apps.FND_CONCURRENT_PROGRAMS_TL c,
    apps.fnd_user d
WHERE   a.concurrent_program_id=b.concurrent_program_id AND
    b.concurrent_program_id=c.concurrent_program_id AND
    a.requested_by=d.user_id AND
    status_code='R' order by Process_time desc;
   
 Gives detail of Concurrent job completed and pending

SELECT
 FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required
--AND referenced_owner = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&Package_name')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND fcr.phase_code NOT IN ( 'C','P');

Gives Detail of Running and Completed Concurrent jobs with Start date and end date 
Latest one at the top

select
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      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,
      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_start_date desc;

--- Query 11 wait events details related with Concurrent programs

SELECT s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
UPPER (s.program) program, s.TYPE, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
FROM v$session s
WHERE ( (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND') AND STATUS='ACTIVE'
)
ORDER BY "PROGRAM";

-- Query 12 To find the pid of the Concurrent job and kill it.

Select a.inst_id, sid, b.spid
from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_ID ='31689665'
AND a.inst_id = b.inst_id and c.os_process_id = a.process;

-- Query 13:To find the Database SID of the Concurrent job
-- We need our concurrent request ID as an input.
-- c.SPID= is the operating system process id
-- d.sid= is the Oracle process id

SQL> column process heading "FNDLIBR PID"
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';


-- Query 14:To find the currently running SQL after finding SID from Query 12

SELECT SQL_TEXT FROM V$SQLAREA WHERE (ADDRESS, HASH_VALUE) IN
(SELECT SQL_ADDRESS, SQL_HASH_VALUE FROM V$SESSION WHERE SID=11710);

-- Query 15:To find what exactly the program,module and the SQL it is executing
--P2 column should change that means Concurrent program is changing blocks and moving

select sid,serial#,p2,program,module from gv$session where sid=11710;

-- Query 16:To find any blocking is there in RAC or non-RAC Database

select process,sid,blocking_session,BLOCKING_INSTANCE from gv$session
where blocking_session is not null;

-- Query17:To check the SID and concurrent program details, where 5991=SID

select sid,serial#,program,module,p2 from gv$session where SID=5991;

-- Query 18: To check time remaining in minutes and elapsed  in minutes for long running job

Select round(sofar*100/totalwork,2)"finished(%)",
Sid,
Serial#,
Opname,
Target,
Sofar,
Totalwork,
Units,
(Time_Remaining/60) Time_Rem_Mins,
(Elapsed_Seconds/60) Elapsed_Time_Mins
From gV$session_Longops
where TIME_REMAINING>0;

--Query 19:To get the location of the trace file for Concurrent request id,eg:The Request ID --(205979614)
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_application_id = execname.application_id
and prog.executable_id=execname.executable_id;

Note:We can use the above query to diagnose the Concurrent job failure by enabling trace on that job first and than running that concurrent job again.

--Query 20:To list the concurrent programs that run today,Giving details about status and logs

SELECT DISTINCT fcp.user_concurrent_program_name,
                fcp.concurrent_program_name,
                fcr.request_id,
                fcr.request_date,
                flv.meaning status,
                fcr.status_code,
                fcr.completion_text,
                fcr.logfile_name,
                fcr.outfile_name,
                fcr.argument_text
  FROM apps.fnd_concurrent_programs_vl fcp,
       apps.fnd_concurrent_requests    fcr,
       apps.fnd_lookup_values          flv
 WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
   AND trunc(fcr.last_update_date) = trunc(SYSDATE)
   AND flv.lookup_code = fcr.status_code
   AND flv.lookup_type = 'CP_STATUS_CODE'
   AND flv.language = 'US'
 ORDER BY fcr.request_date,
          fcr.request_id DESC;

************************************************************

How to find out blocking locks & sessions.
There are many ways to find out blocking session in Oracle or blocking locks in Oracle.You can use following SQL query to find out Blocking locks, User ID, Concurrent Request Name and Object name in oracle applications environment.




SELECT s.inst_id,
NVL (s.username, 'Internal') "Database User",
m.SID,
s.serial#,
p.spid "DB OS Process",
m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server",
s.process "Apps OS process",
m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name",
SQL.sql_text "Statement"
FROM gv$session s,
gv$lock m,
gv$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;


You can also schedule using a crontab entry. Use following shell script and schedule it using crontab. This sheel script will create a html file and mail it to the users.


#------------------Start of script----------------------- #


#!/bin/sh
# Check Blocking Locks
#
. /u01/oracle/ORCL/orcldb/10.2.0/ORCL1_orcl-1.env
echo "Content-Type: text/html" >> Blocking_Session.html

sqlplus -s apps/apps EOF
SELECT s.inst_id, NVL (s.username, 'Internal') "Database User", m.SID,
s.serial#, p.spid "DB OS Process", m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server", s.process "Apps OS process", m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name", SQL.sql_text "Statement"
FROM gv\$session s,
gv\$lock m,
gv\$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv\$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;
spool off
set markup html off spool off
EOF


cat blocking_session.html >> Blocking_Session.html
(echo "Importance: High"; echo "Subject: ORCL: Blocking Session Locks"; cat Blocking_Session.html)
| /usr/sbin/sendmail -F Oracle abc@test.com

rm Blocking_Session.html
rm blocking_session.html

#--------------------End of script----------------------- #


Use following SQL to get more detail data.



SELECT   s.inst_id, NVL (s.username, 'Internal') database_user, s.SID,
         s.event, s.p1, s.serial#, p.spid, m.TYPE,
         DECODE (m.lmode,
                 0, 'None',
                 1, 'Null',
                 2, 'Row Share',
                 3, 'Row Excl.',
                 4, 'Share',
                 5, 'S/Row Excl.',
                 6, 'Exclusive',
                 lmode, LTRIM (TO_CHAR (lmode, '990'))
                ) lock_type,
            m.TYPE
         || ' - '
         || DECODE (m.TYPE,
               'BL', 'Buffer hash table instance lock',
               'CF', ' Control file schema global enqueue lock',
               'CI', 'Cross-instance function invocation instance lock',
               'CS', 'Control file schema global enqueue lock',
               'CU', 'Cursor bind lock',
               'DF', 'Data file instance lock',
               'DL', 'Direct loader parallel index create',
               'DM', 'Mount/startup db primary/secondary instance lock',
               'DR', 'Distributed recovery process lock',
               'DX', 'Distributed transaction entry lock',
               'FI', 'SGA open-file information lock',
               'FS', 'File set lock',
               'HW', 'Space management on a specific segment lock',
               'IN', 'Instance number lock',
               'IR', 'Instance recovery serialization global enqueue lock',
               'IS', 'Instance state lock',
               'IV', 'Library cache invalidation instance lock',
               'JQ', 'Job queue lock',
               'KK', 'Thread kick lock',
               'MB', 'Master buffer hash table instance lock',
               'MM', 'Mount definition gloabal enqueue lock',
               'MR', 'Media recovery lock',
               'PF', 'Password file lock',
               'PI', 'Parallel operation lock',
               'PR', 'Process startup lock',
               'PS', 'Parallel operation lock',
               'RE', 'USE_ROW_ENQUEUE enforcement lock',
               'RT', 'Redo thread global enqueue lock',
               'RW', 'Row wait enqueue lock',
               'SC', 'System commit number instance lock',
               'SH', 'System commit high water mark enqueue lock',
               'SM', 'SMON lock',
               'SN', 'Sequence number instance lock',
               'SQ', 'Sequence number enqueue lock',
               'SS', 'Sort segment lock',
               'ST', 'Space transaction enqueue lock',
               'SV', 'Sequence number value lock',
               'TA', 'Generic enqueue lock',
               'TD', 'DDL enqueue lock',
               'TE', 'Extend-segment enqueue lock',
               'TM', 'DML enqueue lock',
               'TO', 'Temporary Table Object Enqueue',
               'TT', 'Temporary table enqueue lock',
               'TX', 'Transaction enqueue lock',
               'UL', 'User supplied lock',
               'UN', 'User name lock',
               'US', 'Undo segment DDL lock',
               'WL', 'Being-written redo log instance lock',
               'WS', 'Write-atomic-log-switch global enqueue lock',
               'TS', DECODE (m.id2,
                              0, 'Temporary segment enqueue lock (ID2=0)',
                                  'New block allocation enqueue lock (ID2=1)'
                                 ),
               'LA', 'Library cache lock instance lock (A=namespace)',
               'LB', 'Library cache lock instance lock (B=namespace)',
               'LC', 'Library cache lock instance lock (C=namespace)',
               'LD', 'Library cache lock instance lock (D=namespace)',
               'LE', 'Library cache lock instance lock (E=namespace)',
               'LF', 'Library cache lock instance lock (F=namespace)',
               'LG', 'Library cache lock instance lock (G=namespace)',
               'LH', 'Library cache lock instance lock (H=namespace)',
               'LI', 'Library cache lock instance lock (I=namespace)',
               'LJ', 'Library cache lock instance lock (J=namespace)',
               'LK', 'Library cache lock instance lock (K=namespace)',
               'LL', 'Library cache lock instance lock (L=namespace)',
               'LM', 'Library cache lock instance lock (M=namespace)',
               'LN', 'Library cache lock instance lock (N=namespace)',
               'LO', 'Library cache lock instance lock (O=namespace)',
               'LP', 'Library cache lock instance lock (P=namespace)',
               'LS', 'Log start/log switch enqueue lock',
               'PA', 'Library cache pin instance lock (A=namespace)',
               'PB', 'Library cache pin instance lock (B=namespace)',
               'PC', 'Library cache pin instance lock (C=namespace)',
               'PD', 'Library cache pin instance lock (D=namespace)',
               'PE', 'Library cache pin instance lock (E=namespace)',
               'PF', 'Library cache pin instance lock (F=namespace)',
               'PG', 'Library cache pin instance lock (G=namespace)',
               'PH', 'Library cache pin instance lock (H=namespace)',
               'PI', 'Library cache pin instance lock (I=namespace)',
               'PJ', 'Library cache pin instance lock (J=namespace)',
               'PL', 'Library cache pin instance lock (K=namespace)',
               'PK', 'Library cache pin instance lock (L=namespace)',
               'PM', 'Library cache pin instance lock (M=namespace)',
               'PN', 'Library cache pin instance lock (N=namespace)',
               'PO', 'Library cache pin instance lock (O=namespace)',
               'PP', 'Library cache pin instance lock (P=namespace)',
               'PQ', 'Library cache pin instance lock (Q=namespace)',
               'PR', 'Library cache pin instance lock (R=namespace)',
               'PS', 'Library cache pin instance lock (S=namespace)',
               'PT', 'Library cache pin instance lock (T=namespace)',
               'PU', 'Library cache pin instance lock (U=namespace)',
               'PV', 'Library cache pin instance lock (V=namespace)',
               'PW', 'Library cache pin instance lock (W=namespace)',
               'PX', 'Library cache pin instance lock (X=namespace)',
               'PY', 'Library cache pin instance lock (Y=namespace)',
               'PZ', 'Library cache pin instance lock (Z=namespace)',
               'QA', 'Row cache instance lock (A=cache)',
               'QB', 'Row cache instance lock (B=cache)',
               'QC', 'Row cache instance lock (C=cache)',
               'QD', 'Row cache instance lock (D=cache)',
               'QE', 'Row cache instance lock (E=cache)',
               'QF', 'Row cache instance lock (F=cache)',
               'QG', 'Row cache instance lock (G=cache)',
               'QH', 'Row cache instance lock (H=cache)',
               'QI', 'Row cache instance lock (I=cache)',
               'QJ', 'Row cache instance lock (J=cache)',
               'QL', 'Row cache instance lock (K=cache)',
               'QK', 'Row cache instance lock (L=cache)',
               'QM', 'Row cache instance lock (M=cache)',
               'QN', 'Row cache instance lock (N=cache)',
               'QO', 'Row cache instance lock (O=cache)',
               'QP', 'Row cache instance lock (P=cache)',
               'QQ', 'Row cache instance lock (Q=cache)',
               'QR', 'Row cache instance lock (R=cache)',
               'QS', 'Row cache instance lock (S=cache)',
               'QT', 'Row cache instance lock (T=cache)',
               'QU', 'Row cache instance lock (U=cache)',
               'QV', 'Row cache instance lock (V=cache)',
               'QW', 'Row cache instance lock (W=cache)',
               'QX', 'Row cache instance lock (X=cache)',
               'QY', 'Row cache instance lock (Y=cache)',
               'QZ', 'Row cache instance lock (Z=cache)',
               '????'
                   ) lock_type_detail,
         DECODE (m.request,
                 0, 'None',
                 1, 'Null',
                 2, 'Row Share',
                 3, 'Row Excl.',
                 4, 'Share',
                 5, 'S/Row Excl.',
                 6, 'Exclusive',
                 request, LTRIM (TO_CHAR (m.request, '990'))
                ) lock_request,
         DECODE (command,
                 0, 'BACKGROUND',
                 1, 'Create Table',
                 2, 'INSERT',
                 3, 'SELECT',
                 4, 'CREATE CLUSTER',
                 5, 'ALTER CLUSTER',
                 6, 'UPDATE',
                 7, 'DELETE',
                 8, 'DROP',
                 9, 'CREATE INDEX',
                 10, 'DROP INDEX',
                 11, 'ALTER INDEX',
                 12, 'DROP TABLE',
                 13, 'CREATE SEQUENCE',
                 14, 'ALTER SEQUENCE',
                 15, 'ALTER TABLE',
                 16, 'DROP SEQUENCE',
                 17, 'GRANT',
                 18, 'REVOKE',
                 19, 'CREATE SYNONYM',
                 20, 'DROP SYNONYM',
                 21, 'CREATE VIEW',
                 22, 'DROP VIEW',
                 23, 'VALIDATE INDEX',
                 24, 'CREATE PROCEDURE',
                 25, 'ALTER PROCEDURE',
                 26, 'LOCK TABLE',
                 27, 'NO OPERATION',
                 28, 'RENAME',
                 29, 'COMMENT',
                 30, 'AUDIT',
                 31, 'NOAUDIT',
                 32, 'CREATE EXTERNAL DATABASE',
                 33, 'DROP EXTERNAL DATABASE',
                 34, 'CREATE DATABASE',
                 35, 'ALTER DATABASE',
                 36, 'CREATE ROLLBACK SEGMENT',
                 37, 'ALTER ROLLBACK SEGMENT',
                 38, 'DROP ROLLBACK SEGMENT',
                 39, 'CREATE TABLESPACE',
                 40, 'ALTER TABLESPACE',
                 41, 'DROP TABLESPACE',
                 42, 'ALTER SESSION',
                 43, 'ALTER USER',
                 44, 'COMMIT',
                 45, 'ROLLBACK',
                 46, 'SAVEPOINT',
                 47, 'PL/SQL EXECUTE',
                 48, 'SET TRANSACTION',
                 49, 'ALTER SYSTEM SWITCH LOG',
                 50, 'EXPLAIN',
                 51, 'CREATE USER',
                 52, 'CREATE ROLE',
                 53, 'DROP USER',
                 54, 'DROP ROLE',
                 55, 'SET ROLE',
                 56, 'CREATE SCHEMA',
                 57, 'CREATE CONTROL FILE',
                 58, 'ALTER TRACING',
                 59, 'CREATE TRIGGER',
                 60, 'ALTER TRIGGER',
                 61, 'DROP TRIGGER',
                 62, 'ANALYZE TABLE',
                 63, 'ANALYZE INDEX',
                 64, 'ANALYZE CLUSTER',
                 65, 'CREATE PROFILE',
                 66, 'DROP PROFILE',
                 67, 'ALTER PROFILE',
                 68, 'DROP PROCEDURE',
                 69, 'DROP PROCEDURE',
                 70, 'ALTER RESOURCE COST',
                 71, 'CREATE SNAPSHOT LOG',
                 72, 'ALTER SNAPSHOT LOG',
                 73, 'DROP SNAPSHOT LOG',
                 74, 'CREATE SNAPSHOT',
                 75, 'ALTER SNAPSHOT',
                 76, 'DROP SNAPSHOT',
                 79, 'ALTER ROLE',
                 85, 'TRUNCATE TABLE',
                 86, 'TRUNCATE CLUSTER',
                 87, '-',
                 88, 'ALTER VIEW',
                 89, '-',
                 90, '-',
                 91, 'CREATE FUNCTION',
                 92, 'ALTER FUNCTION',
                 93, 'DROP FUNCTION',
                 94, 'CREATE PACKAGE',
                 95, 'ALTER PACKAGE',
                 96, 'DROP PACKAGE',
                 97, 'CREATE PACKAGE BODY',
                 98, 'ALTER PACKAGE BODY',
                 99, 'DROP PACKAGE BODY',
                 command || ' - ???'
                ) command,
         (CASE
             WHEN m.TYPE = 'UL'
                THEN 'None '
             ELSE DECODE (command,
                          0, 'None',
                          DECODE (m.id2,
                                  0,  dusr.username
                                   || '.'
                                   || SUBSTR (dobj.NAME, 1, 30),
                                  'Rollback Segment'
                                 )
                         )
          END
         ) OBJECT,
      s.machine, s.process, m.ctime, s.program, SQL.sql_text, s.sql_id,
         (SELECT    'select * from '
                 || owner
                 || '.'
                 || object_name
                 || ' where rowid=dbms_rowid.rowid_create( 1, '
                 || row_wait_obj#
                 || ','
                 || row_wait_file#
                 || ','
                 || row_wait_block#
                 || ','
                 || row_wait_row#
                 || ' );'
            FROM dba_objects db
           WHERE db.object_id = s.row_wait_obj# AND db.object_type = 'TABLE') row_wait
    FROM gv$session s,
         gv$lock m,
         gv$process p,
         gv$sqlarea SQL,
         dba_users dusr,
         SYS.obj$ dobj
   WHERE m.id1 IN (SELECT il.id1
                     FROM gv$lock il
                    WHERE il.request <> 0)
     AND m.SID = s.SID
     AND s.paddr = p.addr
     AND s.inst_id = p.inst_id
     AND SQL.inst_id(+) = s.inst_id
     AND SQL.address(+) = s.sql_address
     AND SQL.hash_value(+) = s.sql_hash_value
     AND m.inst_id = s.inst_id
     AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
     AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;


***********************************************************************

If any Concurrent request is slow or takes more than normal time to complete,follow the below steps first to verify ..

HighLevel Steps..
SQL >select * from v$session where process in(select os_process_id from fnd_concurrent_requests where request_id='<Req Id>');

Get SID from above sql.
Take that sid and find object_id's from below command
SQL >select * from v$locked_objects where session_id='SID'
Now take all the object_id's and check any other session is locking those object_ids apart from the above SID...
SQL >select * from v$locked_objects where object_id =''
if any session is locking check the status of that session from v$session.
if that is inactive kill that session ... to free up the resource
SQL >alter system kill session 'SID,SERIAL#';












ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE

 Error:- IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1) SYMPTOMS:- Yo...