How to Monitor Undo Tablespace and Segment Usage

Starting from release 9i oracle introduced Automatic Undo Management to help DBAs and Developers to cope with ORA-01555: snapshot too old error. Ofcourse you can't completely avoid ORA-01555 error but you can tweak some parameters with Automatic Undo Management along with application code tuning to reduce it, Parameters like UNDO_RETENTION parameter, which defines time in minutes how long oracle will keep the committed transactions in UNDO segments.
One should use UNDO advisor in order to calculate optimum UNDO tablespace Size and UNDO_RETENTION parameter value.
Use following SQL script to monitor Undo tablespace and Undo Segments or Rollback Segments usage in Oracle Database.

------------Start of Script-------------------------------------
#!/bin/sh
# Undo Segment Usage
#

SPOOL_FILE=undo_seg.html
FINAL_FILE=Undo_Segments.html
echo "Content-Type: text/html" >> ${FINAL_FILE}
sqlplus -s "/ as sysdba" EOF
set echo off
set pagesize 220
set markup html on spool on
spool ${SPOOL_FILE}
TTITLE CENTER 'Undo Segments Usage'
SELECT gvs.inst_id "Instance",
gvs.SID,
glo.os_user_name "OS User",
glo.oracle_username "DB User",
dbo.owner "Schema",
SUBSTR (dbo.object_name, 1, 30) "Object Name",
SUBSTR (dbo.object_type, 1, 10) "Object Type",
SUBSTR (drs.segment_name, 1, 15) "RBS Name",
gvt.used_urec "# of Records",
gvt.used_ublk "# of Blocks",
drs.tablespace_name "Tablespace"
FROM
gv$locked_object glo,
dba_objects dbo,
dba_rollback_segs drs,
gv$transaction gvt,
gv$session gvs
WHERE glo.object_id = dbo.object_id
AND glo.xidusn = drs.segment_id
AND glo.xidusn = gvt.xidusn
AND glo.xidslot = gvt.xidslot
AND gvt.addr = gvs.taddr
AND gvt.used_ublk > 1000
ORDER BY gvt.used_ublk DESC;
spool off
set markup html off spool off
EOF

if [ `grep -c 'no rows selected' ${SPOOL_FILE}` -eq 1 ]
then
rm ${FINAL_FILE}
rm ${SPOOL_FILE}
exit 0
fi

cat ${SPOOL_FILE} | grep -v 'rows selected' >> ${FINAL_FILE}

(echo "Importance: High"; echo "Subject: $ORACLE_SID: Undo Segment Usage"; cat ${FINAL_FILE}) | /usr/sbin/sendmail -F $ORACLE_SID test@testorcl.com
rm ${FINAL_FILE}
rm ${SPOOL_FILE}

------------Start of Script--------------------------------------

How to Resolve ORA-27063 ORA-01114 ORA-01110 Errors

During hardware failure we got following error while starting one of our instances.

ALTER DATABASE OPEN
Thu Aug 6 23:21:00 2009
Errors in file /u01_orcltest/db/10.2.0/admin/ORCLTEST_orcltestdb01/udump/orcltest_ora_8866.trc:
ORA-01110: data file 407: '/u02_orcltest/data/apps_ts_queues_001.dbf'
ORA-01114: IO error writing block to file 407 (block # 1)
ORA-27063: number of bytes read/written is incorrect
Additional information: 8192
Thu Aug 6 23:21:00 2009
ORA-01201: file 407 header failed to write correctly
ORA-1113 signalled during: ALTER DATABASE OPEN...

We thought that the above file got currepted and we need to do media recovery and when we tried to recover database we got the same error again.

Thu Aug 6 23:48:28 2009
ALTER DATABASE RECOVER database
Thu Aug 6 23:48:28 2009
Media Recovery Start
Thu Aug 6 23:48:28 2009
Errors in file /u01_orcltest/db/10.2.0/admin/ORCLTEST_orcltestdb01/udump/orcltest_ora_13199.trc:
ORA-01110: data file 407: '/u02_orcltest/data/apps_ts_queues_001.dbf'
ORA-01114: IO error writing block to file 407 (block # 1)
ORA-27063: number of bytes read/written is incorrect
Additional information: 8192
Thu Aug 6 23:48:28 2009
Media Recovery failed with error 1201
ORA-283 signalled during: ALTER DATABASE RECOVER database ...


At this point we asked our Unix team to unmount and mount file system again for this instance. Once file system remounted we tried to open database but failed with ORA error again. In order to save our database at this point we recovered database using online redo log files.

ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile until cancel ...
Fri Aug 7 01:33:12 2009
ALTER DATABASE RECOVER LOGFILE '/u02_orcltest/data/log01a.log'
Fri Aug 7 01:33:12 2009
Media Recovery Log /u02_orcltest/data/log01a.log
Fri Aug 7 01:33:14 2009
Incomplete recovery applied all redo ever generated.
Recovery completed through change 1136061258397
Fri Aug 7 01:33:14 2009
Media Recovery Complete (ORCLTEST)
Completed: ALTER DATABASE RECOVER LOGFILE '/u02_orcltest/data/log01a.log'
Fri Aug 7 01:33:26 2009
alter database open resetlogs
Fri Aug 7 01:33:27 2009
RESETLOGS after complete recovery through change 1136061258397
Resetting resetlogs activation ID 2533705982 (0x970548fe)

How to Resolve ORA-00600: [kkoarl1]

Here you go again with ORA-00600: internal error code, arguments: [kkoarl1], [90139], [46226], [], [], [], [], [] error in our production database followed by "Memory Notification: Library Cache Object loaded into SGA" warning message. Luckily this error is not serious and we have a workaround also for ORA-00600 error and warning message.

As per Oracle one can see this error with very large and complex query. In our case we got this error from very complex and very large Discoverer report.

ORA-00600: internal error code, arguments: [kkoarl1], [90139], [46226], [], [], [], [], []Current SQL statement for this session:SELECT CASE WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) = '500000' THEN '220270' WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) = '500001' THEN '220270' WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) = '505000' THEN '220250' WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) = '505001' THEN '220250' WHEN ( SUBSTR ----------------
----------------
----------------
FROM POFG_PURCHASE_ORDERS o102467,en') as C_2,NVL(o102467.AUTHORIZATION_STATUS,'Incomplete') as C_1,ROUND(o102468.ORDERED_Q POFG_PO_DISTRIBUTIONS o102468,_5,ROUND(o102468.DELIVERED_QUANTITY,2)*o102470.ACTUAL_PRICE as C_6,ROUND(o102468.BILLED_ POFG_PO_LINES o102469,RICE as C_7
-------------------
-------------------
-------------------

This ORA-00600 error can be resolved by using "_optimizer_cost_based_transformation" parameter and set it false.

"_optimizer_cost_based_transformation"=false

During 11i upgrade also we had many standard forms failing with different errors and we end up setting this parameter to false in 11i e-Business suite. Although some experts says not to change this parameter because of performance issue and work on the the sql statment but we didn't have any performance issue after making changes to this parameter.

Memory Notification: Library Cache Object loaded into SGA

Memory Notification: Library Cache Object loaded into SGA
Heap size 109275K exceeds notification threshold (51200K)Details in trace file ORCLTEST_ora_19128.trc
KGL object name :EXPLAIN PLAN SET STATEMENT_ID = '117984602' INTO EUL4_US.EUL5_PLAN_TABLE FORSELECT CASE WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) IN ('785500','803500','810000','810100','810500','810510','810520','810530',
'810540','810550','810570','811010','823250','823500','823501','830500',
'831000','831100','831300','831400','840000','840001','840002','840003',
'840050','840070','840100','840120','840150','840200','840250','840300',
'840350','840400','840450','840500','840550','840600','840650','840700',
-----------
-----------

This is a simple warning message for KGL heap size warning threshold as some process is waiting for a long time in finding free memory extents. This warning message can be supress by using _kgl_large_heap_warning_threshold (hidden parameter) and set it to relatively high value in bytes, default value of this parameter is 50M in 10.2.0.4 .0. You may also try setting this parameter to zero to supress this warning.

How to Resolve ORA-00600: [sorsikbeg_1]

Now we got another ORA-00600 Error in our production database and this time error was ORA-00600: internal error code, arguments: [sorsikbeg_1], [5], [0], [], [], [], []

This error is generally caused by "CONNECT BY PRIOR" in SQL statement. In our trace file we could see the following connect by query:

SELECT COUNT(DISTINCT PAAF.PERSON_ID)
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERSON_TYPES PPT,
PER_PERSON_TYPE_USAGES_F PPTU,
PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE 1 = 1 AND PAPF.PERSON_ID = PPTU.PERSON_ID
AND PPTU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE = 'EMP'
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID AND
PAST.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
AND PAAF.ASSIGNMENT_TYPE = 'E'
CONNECT BY PRIOR PAAF.PERSON_ID = PAAF.SUPERVISOR_ID
AND TRUNC(SYSDATE)
BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE AND
TRUNC(SYSDATE) BETWEEN PPTU.EFFECTIVE_START_DATE AND PPTU.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND
PAAF.EFFECTIVE_END_DATE START WITH 1 = 1 AND PAAF.PERSON_ID = :b1;

In order to resolve this error we temporary used following work around (Oracle Hint) but be careful as this hint can have serious performance issue.

SELECT /*+ NO_CONNECT_BY_FILTERING*/ COUNT(DISTINCT PAAF.PERSON_ID) FROM
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERSON_TYPES PPT, PER_PERSON_TYPE_USAGES_F PPTU, PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE 1 = 1
AND PAPF.PERSON_ID =PPTU.PERSON_ID
AND PPTU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE = 'EMP'
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
AND PAST.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
AND PAAF.ASSIGNMENT_TYPE = 'E'
CONNECT BY PRIOR
PAAF.PERSON_ID = PAAF.SUPERVISOR_ID
AND TRUNC(SYSDATE)BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE ANDTRUNC(SYSDATE) BETWEEN PPTU.EFFECTIVE_START_DATE AND PPTU.EFFECTIVE_END_DATEAND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE ANDPAAF.EFFECTIVE_END_DATE START WITH 1 = 1
AND PAAF.PERSON_ID = :b1;

another way to resolve this is to set the parameter "_optimizer_connect_by_cost_based" to false in the session itself using
alter session set "_optimizer_connect_by_cost_based"=FALSE by default value of this hidden parameter is TRUE, but one should be careful with hidden parameters and should always get blessing from oracle before using them otherwise you might get unpredictable results.

How to find out Patch Information and Duration in Oracle Applications 11i

Use following SQL Query to find out Installed Patch Information and duration.

SELECT AAP.PATCH_NAME "Patch Name",
NVL
(DECODE
(MERGED_DRIVER_FLAG,
'Y', AD_PA_VALIDATE_CRITERIASET.GET_CONCAT_MERGEPATCHES
(APD.PATCH_DRIVER_ID), '' ), 'Single Patch' ) "Merged Patches",
APR.START_DATE "Start Date", APR.END_DATE "End Date",
APD.DRIVER_FILE_NAME "Driver File",
APR.PATCH_ACTION_OPTIONS "Patch Options",
DECODE (APR.SERVER_TYPE_ADMIN_FLAG,
'Y', 'Admin,',
NULL
)
|| DECODE (APR.SERVER_TYPE_FORMS_FLAG, 'Y', 'Forms,', NULL)
|| DECODE (APR.SERVER_TYPE_NODE_FLAG, 'Y', 'Node,', NULL)
|| DECODE (APR.SERVER_TYPE_WEB_FLAG, 'Y', 'Web,', NULL)
"Server Type",
APD.PLATFORM "Platform",

SUBSTR (APR.PATCH_TOP, 1, 20) "Patch Top",
AD_CORE.GET_FORMATTED_ELAPSED_TIME
((APR.END_DATE - APR.START_DATE), 2 ) "Elapsed Time"
FROM AD_PATCH_RUNS APR,
AD_PATCH_DRIVER_LANGS APDL,
AD_PATCH_DRIVERS APD,
AD_APPLIED_PATCHES AAP
WHERE APD.PATCH_DRIVER_ID = APR.PATCH_DRIVER_ID
AND APD.PATCH_DRIVER_ID = APDL.PATCH_DRIVER_ID
AND APD.APPLIED_PATCH_ID = AAP.APPLIED_PATCH_ID
AND AAP.PATCH_NAME = &PATCH_NAME
ORDER BY END_DATE DESC;






Use following SQL query to find out Patch Job Details and Timings.


SELECT PROGRAM "Program", AAP.PATCH_NAME "Patch Name",
APRT.JOB_NAME "Job Name", APRT.PHASE_NAME "Phase",
APRT.START_TIME "Job Start Time",
APRT.END_TIME "Job End Time",
ROUND ((NVL
(APRT.END_TIME, SYSDATE) - APRT.START_TIME)
* 60 * 24, 2) "Elapsed Time"
FROM AD_PROGRAM_RUN_TASK_JOBS APRT,
AD_PATCH_RUNS APR,
AD_PATCH_DRIVERS APD,
AD_APPLIED_PATCHES AAP
WHERE APRT.SESSION_ID = APR.SESSION_ID
AND APR.PATCH_DRIVER_ID = APD.PATCH_DRIVER_ID
AND APD.APPLIED_PATCH_ID = AAP.APPLIED_PATCH_ID
AND AAP.PATCH_NAME = &PATCH_NAME
ORDER BY "Elapsed Time" DESC

How to Find Out Pending Concurrent Requests in Oracle Applications 11i

  
   Use following script to monitor Pending concurrent requests status in the oracle applications 11i. You can schedule this using crontab or use only sql script to get the results. This script will help you to understand pending concurrent requests status and to resolve any issue with pending concurrent requests.

------------Start of Script-------------------------------------


#!/bin/sh
# Long Running Concurrent Requests
#

SPOOL_FILE=/home/oracle/scripts/pending_conc.html
FINAL_FILE=/home/oracle/scripts/Pending_Concurrent.html

echo "Content-Type: text/html" >> ${FINAL_FILE}
sqlplus -s apps/apps EOF
set echo off
set pagesize 120
set markup html on spool on
spool ${SPOOL_FILE}
TTITLE CENTER 'Pending Concurrent Requests '



SELECT DISTINCT far.request_id, SUBSTR (program, 1, 30), far.user_name,
                far.phase_code, far.status_code, phase, status,
                (CASE
                    WHEN far.phase_code = 'P' AND far.hold_flag = 'Y'
                       THEN 'Job is on Hold by user'
                    WHEN far.phase_code = 'P'
                    AND (far.status_code = 'I' OR far.status_code = 'Q')
                    AND far.requested_start_date > SYSDATE
                       THEN    'Job is scheduled to run at '
                            || TO_CHAR (far.requested_start_date,
                                        'DD-MON-RR HH24:MI:SS'
                                       )
                    WHEN far.phase_code = 'P'
                    AND (far.status_code = 'I' OR far.status_code = 'Q')
                    AND fcp.queue_control_flag = 'Y'
                       THEN 'ICM will run ths request on its next sleep cycle'
                    WHEN far.phase_code = 'P' AND far.status_code = 'P'
                       THEN 'Scheduled to be run by the Advanced Scheduler'
                    WHEN far.queue_method_code NOT IN ('I', 'B')
                       THEN    'Bad queue_method_code of: '
                            || far.queue_method_code
                    WHEN far.run_alone_flag = 'Y'
                       THEN 'Waiting on a run alone request'
                    WHEN far.queue_method_code = 'B'
                    AND far.status_code = 'Q'
                    AND EXISTS (
                           SELECT 1
                             FROM fnd_amp_requests_v farv
                            WHERE phase_code = 'P'
                              AND program_application_id =
                                                    fcps.to_run_application_id
                              AND concurrent_program_id =
                                             fcps.to_run_concurrent_program_id)
              THEN    'Incompatible request '
                  || (SELECT DISTINCT    farv.request_id
                                || ': '
                                || farv.program
                                || ' is Ruuning by : '
                                || farv.user_name
                      FROM fnd_amp_requests_v farv,fnd_concurrent_program_serial fcps1
                      WHERE phase_code = 'R'
                      AND program_application_id =fcps1.running_application_id
                      AND concurrent_program_id =fcps1.running_concurrent_program_id
                      AND fcps.to_run_application_id=fcps1.to_run_application_id
                      AND fcps.to_run_concurrent_program_id=fcps1.to_run_concurrent_program_id)
                    WHEN fcp.enabled_flag = 'N'
                       THEN 'Concurrent program is disabled'
                    WHEN far.queue_method_code = 'I' AND far.status_code = 'Q'
                       THEN 'This Standby request might not run'
                    WHEN far.queue_method_code = 'I' AND far.status_code = 'I'
                       THEN    'Waiting for next available '
                            || fcqt.user_concurrent_queue_name
                            || ' process to run the job. Estimate Wait time '
                            || fcq.sleep_seconds
                            || ' Seconds'
                    WHEN far.queue_method_code = 'I'
                    AND far.status_code IN ('A', 'Z')
                       THEN    'Waiting for Parent Request: '
                            || NVL (far.parent_request_id,
                                    'Could not locate Parent Request ID'
                                   )
                    WHEN far.queue_method_code = 'B' AND far.status_code = 'Q'
                       THEN 'Waiting on the Conflict Resolution Manager'
                    WHEN far.queue_method_code = 'B' AND far.status_code = 'I'
                       THEN    'Waiting for next available '
                            || fcqt.user_concurrent_queue_name
                            || ' process to run the job. Estimate Wait time '
                            || fcq.sleep_seconds
                            || ' Seconds'
                    WHEN far.phase_code = 'P' AND far.single_thread_flag = 'Y'
                       THEN 'Single-threaded request. Waiting on other requests for this user.'
                    WHEN far.phase_code = 'P' AND far.request_limit = 'Y'
                       THEN 'Concurrent: Active Request Limit is set. Waiting on other requests for this user.'
                 END
                ) reason
           FROM fnd_amp_requests_v far,
                fnd_concurrent_programs fcp,
                fnd_conflicts_domain fcd,
                fnd_concurrent_program_serial fcps,
                fnd_concurrent_queues fcq,
                fnd_concurrent_queue_content fcqc,
                fnd_concurrent_queues_tl fcqt
          WHERE far.phase_code = 'P'
            AND far.concurrent_program_id = fcp.concurrent_program_id
            AND fcd.cd_id = far.cd_id
            AND fcps.running_application_id(+) = far.program_application_id
            AND fcps.running_concurrent_program_id(+) = far.concurrent_program_id
            AND far.program_application_id = fcps.to_run_application_id(+)
            AND far.concurrent_program_id = fcps.to_run_concurrent_program_id(+)
            AND far.concurrent_program_id = fcqc.type_id(+)
            AND far.program_application_id = fcqc.type_application_id(+)
            AND fcq.concurrent_queue_id(+) = fcqc.concurrent_queue_id
            AND fcq.application_id(+) = fcqc.queue_application_id
            AND fcqt.concurrent_queue_id(+) = fcq.concurrent_queue_id
            AND fcqt.application_id(+) = fcq.application_id
       ORDER BY far.request_id DESC
/
spool off
set markup html off spool off
EOF

if [ `grep -c 'no rows selected' ${SPOOL_FILE}` -eq 1 ]
then
rm ${FINAL_FILE}
rm ${SPOOL_FILE}
exit 0
fi
cat ${SPOOL_FILE} | grep -v 'rows selected' >> ${FINAL_FILE}
(echo "Importance: High"; echo "Subject: ORCL: Pending Concurrent Requests"; cat ${FINAL_FILE}) | /usr/sbin/sendmail -F ORCL
test@testorcl.com

rm ${FINAL_FILE}
rm ${SPOOL_FILE}

-------------End of Script-------------------------------------

Hourly Concurrent Managers/Requests Performance Report in Oracle Applications 11i

In order to fine tune your Concurrent Managers performance you may need to collect hourly data for Concurrent Managers and Concurrent Requests. Please use following SQL report to generate this data.

COLUMN "Queue Time" format a15
COLUMN "Concurrent Manager" format a20
COLUMN "Program" format a60 heading "Concurrent Program"
set linesize 2000
set echo off
BREAK on "Queue Time" SKIP 1 ON "Concurrent Manager"
SELECT
TO_CHAR (actual_start_date, 'DD-MON-YY : HH24') "Queue Time",
fcqtl.user_concurrent_queue_name "Concurrent Manager",
fcptl.user_concurrent_program_name "Program",
ROUND
( SUM (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Total Duration (min)",
ROUND
( AVG (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Avg Duration (min)",
ROUND
( MIN (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Min Duration (min)",
ROUND
( MAX (GREATEST (actual_completion_date - actual_start_date, 0))
* 60
* 24,
2
) "Max Duration (min)",
COUNT (*) "Times Run", fcq.target_processes "Total Processes"
FROM fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcptl,
fnd_concurrent_processes fcproc,
fnd_concurrent_queues_tl fcqtl,
fnd_concurrent_queues fcq,
fnd_concurrent_requests fcr
WHERE fcr.phase_code = 'C'
AND fcr.actual_completion_date IS NOT NULL
AND actual_start_date IS NOT NULL
AND fcq.concurrent_queue_id = fcproc.concurrent_queue_id
AND fcq.application_id = fcproc.queue_application_id
AND fcq.manager_type = 1
AND fcr.controlling_manager = fcproc.concurrent_process_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcp.concurrent_program_name NOT IN
('ACTIVATE', 'ABORT', 'DEACTIVATE', 'VERIFY')
AND fcr.concurrent_program_id = fcptl.concurrent_program_id
AND fcr.program_application_id = fcptl.application_id
AND fcptl.LANGUAGE = 'US'
AND fcproc.queue_application_id = fcqtl.application_id
AND fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
AND fcqtl.LANGUAGE = 'US'
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YY : HH24'),
fcqtl.user_concurrent_queue_name,
fcptl.user_concurrent_program_name,
fcq.target_processes
ORDER BY "Queue Time" ASC,
"Concurrent Manager" ASC,
"Times Run" DESC,
"Max Duration (min)" DESC,
"Total Duration (min)" DESC

Generate Graph for you presentation using MS Excel

How to List Responsibility And Request Group in Oracle Applications 11i

   Use following SQL Report to list responsibilities and attached request groups. In oracle apps Users submit concurrent request based on the Request group assigned to thier responsibility.

COLUMN responsibility_name format a40 heading "Responsibility Name"
COLUMN request_group_name format a40 heading "Request Group Name"
COLUMN description format a60 heading "Request Group Detail"
BREAK on responsibility_name SKIP 1 ON request_group_name SKIP 1
SET feedback off
SET linesize 1000
SELECT 'Report Date: ' SYSDATE FROM DUAL;
SELECT frv.responsibility_name,
frg.request_group_name,
frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;


Use Following SQL report to list Responsibilities, Request Group and Concurrent requests for each Request Group.


COLUMN responsibility_name format a40 heading "Responsibility Name"
COLUMN request_group_name format a40 heading "Request Group Name"
COLUMN DESCR format a60 heading "Request Group Detail"
COLUMN user_concurrent_program_name format a60 heading "Concurrent Program"
COLUMN Description format a60 heading "Concurrent Program Detail"
BREAK on responsibility_name SKIP 1 ON request_group_name SKIP 1 on DESCR
SET feedback off
SET linesize 1000
SELECT 'Report Date: ' SYSDATE FROM DUAL;
SELECT frv.responsibility_name, frg.request_group_name,
frg.description descr, fcpv.user_concurrent_program_name,
fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name, frg.request_group_name, frg.description

How to List Reponsibilities for an User in Oracle Applications 11i

You can use following SQL Statments to list responsibities assigned to all users or to a particuler user instead of going to System Administrator Screen.


For All Users

SELECT UNIQUE fu.user_id "User ID",
fu.user_name "User Name",
frt.responsibility_name "Responsibility Name",
frv.description "Responsibility Detail",
fat.application_name "Application Name"
FROM fnd_user fu,
fnd_user_resp_groups furg,
fnd_application_tl fat,
fnd_responsibility_tl frt,
fnd_responsibility_vl frv
WHERE furg.user_id(+) = fu.user_id
AND furg.responsibility_application_id = fat.application_id
AND fat.application_id = frt.application_id
AND furg.responsibility_id = frt.responsibility_id
AND furg.responsibility_application_id = frv.application_id
AND furg.responsibility_id = frv.responsibility_id
ORDER BY fu.user_name,
fat.application_name,
frt.responsibility_name


For particuler User
 SELECT UNIQUE fu.user_id "User ID",
fu.user_name "User Name",
frt.responsibility_name "Responsibility Name",
frv.description "Responsibility Detail",
fat.application_name "Application Name"
FROM fnd_user fu,
fnd_user_resp_groups furg,
fnd_application_tl fat,
fnd_responsibility_tl frt,
fnd_responsibility_vl frv
WHERE furg.user_id(+) = fu.user_id
AND furg.responsibility_application_id = fat.application_id
AND fat.application_id = frt.application_id
AND furg.responsibility_id = frt.responsibility_id
AND furg.responsibility_application_id = frv.application_id
AND furg.responsibility_id = frv.responsibility_id
And fu.USER_NAME = &Username

How to Resolve ORA-04061: Existing State Of Package Body has been invalidated

In Oracle application Workflow related errors are very common specially related to PO Approval or Requisition approval. Recently we got ORA-04061: Existing State Of Package Body has been invalidated for APPS.PO_GA_PVT and APPS.PO_WF_PO_NOTIFICATION. All PO related Workflow notifications were erroring out and users were not able to approve PO from emails and from the system.

Error Message:

[WF_ERROR] ERROR_MESSAGE=3835: Error '-20002 - ORA-20002: 2018: Unable to generate the notification XML. Caused by: 2020: Error when getting notification content. Caused by: ORA-04061: existing state of has been invalidated ORA-04061: existing state of package body "APPS.XXUS_PO_WF_PO_NOTIFICATION" has been invalidated ORA-04065: not executed, altered or dropped package body "APPS.XXUS_PO_WF_PO_NOTIFICATION" ORA-06508: PL/SQL: could not find program unit being called: "APPS.XXUS_PO_WF_PO_NOTIFICATION" wf_notification.GetAttrDoc2(3618679, PO_LINES_DE' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK= WF_MAIL.GetLOBMessage3(3618679, WFMAIL, 2020: Error when getting notification content. Caused by: ORA-04061: existing state of has been invalidated ORA-04061: existing state of package body "APPS.XXUS_PO_WF_PO_NOTIFICATION" has been invalidated ORA-04065: not executed, altered or dropped package body "APPS.XXUS_PO_WF_PO_NOTIFICATION" ORA-06508: PL/SQL: could not find program unit being called: "APPS.XXUS_PO_WF_PO_NOTIFICATION" wf_notification.GetAttrDoc2(3618679, PO_LINES_DETAILS, text/plain) Wf_Notification.GetAttrDoc(3618679, PO_LINES_DETAILS, text/plain) Wf_Notification.GetText(3618679, text/plain) Wf_Notification.GetBody(3618679, text/plain) WF_NOTIFICATION.GetFullBody(nid => 3618679, disptype => text/plain) WF_MAIL.GetLOBMessage3(nid => 3618679, r_ntf_pref => MAILATTH), Step -> Getting text/plain body) WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 3618679) WF_XML.Generate(oracle.apps.wf.notification.send, 3618679) WF_XML.Generate(oracle.apps.wf.notification.send, 3618679) Wf_Event.setMessage(oracle.apps.wf.notification.send, 3618679, WF_XML.Generate) Wf_Event.dispatch_internal()

In the database both the package and body were in valid state. We completed following steps as per metalink note to resolve this issue but couldn't resolve this issue.

1. Shutdown the Notification Mailer and agent listener
2. Recompile the APPS schema through ADADMIN (even when there were no invalids)
3. Start the Notification Mailer and agent listener

Now to resolve this issue we need to do following steps in addition to the above steps.

1. Shutdown the Notification Mailer and agent listener
2. Recompile the APPS schema through ADADMIN
3. connect / as sysdba

SQL>alter system set aq_tm_processes=0 scope=memory;

You need to wait until the q00* and qmnc* processeses are no longer running. You can check via

ps -ef grep q00 and ps -ef grep qmn

SQL> alter system flush shared_pool;

SQL> alter system set aq_tm_processes=1 scope=memory;

4. Start the Notification Mailer and agent listener

Once we did all the above steps users were able to receive mails related to PO and Requisition and they were also able to approve PO from Workflow Notification Emails.

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;