Check Database Locks

--To check database locks----[Kill Inactive Session] ----------------------------------------------------
SELECT * FROM dba_locks WHERE blocking_others='Blocking';

select LOCK_TYPE,SESSION_ID,BLOCKING_OTHERS from dba_locks where BLOCKING_OTHERS !='Not Blocking';

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

--Locked tables
SELECT l.session_id sid, o.object_name, o.object_type
FROM v$locked_object l, all_objects o
WHERE l.object_id = o.object_id

select SID,SERIAL#,MODULE,ACTION,CLIENT_IDENTIFIER,BLOCKING_SESSION_STATUS,STATE from v$session
where status='INACTIVE'
--where CLIENT_IDENTIFIER='%MONIR%'
AND MODULE like '%BOM%'
AND STATE='WAITING'
AND CLIENT_IDENTIFIER !='SYSADMIN'


--get SERIAL# where SID is from the above script
select * from v$session where SID=6563


--To kill the session with seesion id------------------------------------------------------------------
ALTER SYSTEM KILL SESSION '1351,993';

select * from v$session where process='144'



SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name 
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID


select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id;

SELECT a.object_id, a.session_id, substr(b.object_name, 1, 40)
FROM v$locked_object a, dba_objects b
WHERE a.object_id = b.object_id
AND b.object_name like 'BOM%'
ORDER BY b.object_name;




SELECT l.*, o.owner object_owner, o.object_name
FROM SYS.all_objects o, v$lock l
WHERE l.TYPE = 'TM'
AND o.object_id = l.id1
AND o.object_name in ('AP_INVOICES_ALL', 'AP_INVOICE_LINES_ALL', 'AP_INVOICE_DISTRIBUTIONS_ALL');




SELECT SID, SERIAL#
FROM v$session
WHERE SID = 960;



--R12: APXPAWKB Cannot Select this Payment Document Because it is in use By Another Single Payment (Doc ID 1322570.1)
--In the instance where user can reproduce the issue run the following queries:

1)

select * from dba_objects where object_name like 'CE_PAYMENT_DOCUMENTS'
and owner = 'CE';

2)

select * from v$locked_object where object_id in (select object_id from
dba_objects where object_name like 'CE_PAYMENT_DOCUMENTS'and owner = 'CE');

3)

select * from v$session where sid in (select session_id from
v$locked_object where object_id in (select object_id from dba_objects where
object_name like 'CE_PAYMENT_DOCUMENTS'and owner = 'CE'));

4)

select * from dba_locks where session_id in (select session_id from
v$locked_object where object_id in (select object_id from dba_objects where
object_name like ‘CE_PAYMENT_DOCUMENTS’and owner = ‘CE’));

Query result 3 provides the session which is having the lock of payment document.

Kill the session which is locking the 'CE_PAYMENT_DOCUMENTS' table

alter system kill session 'session,serial';

Cancel Scheduled Concurrent requests

--How to Cancel a Concurrent Request Stuck in the Queue? [ID 749748.1]
--CCM.sql Diagnostic Script for Concurrent Manager [ID 171855.1]
--CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables [ID 134007.1]
--How to Clear a Request Set Stuck in RUNNING / PAUSED Status [ID 1081912.6]

--STATUS_CODE Column:
--A Waiting  B Resuming C Normal D Cancelled E Error F Scheduled G Warning H On Hold I Normal M No Manager
--Q Standby R Normal S Suspended T Terminating U Disabled W Paused X Terminated Z Waiting

--PHASE_CODE column
--C Completed I Inactive P Pending R Running

update FND_CONCURRENT_REQUESTS set phase_code='C',status_code='D' where phase_code='R' and requested_by in ('1187','1158','2842','3481')

commit

select * from FND_CONCURRENT_REQUESTS where phase_code='R' and requested_by in ('1757','1197','1186','1185')

--Update a specifix concurrent program
update FND_CONCURRENT_REQUESTS set phase_code='C',status_code='D' where phase_code='R' --and requested_by in ('1757','1197','1186','1185')
and REQUEST_ID in ('12095604','12095598','12095589')

commit

select * from FND_CONCURRENT_REQUESTS where phase_code='R' --and requested_by in ('1757','1197','1186','1185')
and REQUEST_ID in ('11150939','11134004','11130061')


-- To get the list of Scheduled Concurrent Requests ------------------------------------------------------------

SELECT
a.request_id,
A.REQUESTED_BY,
a.program,
a.argument_text,
a.requested_start_date,
b.resubmit_interval,
b.resubmit_interval_unit_code,
a.requestor
FROM  apps.fnd_conc_req_summary_v a,    apps.fnd_concurrent_requests b
WHERE a.phase_code='P' AND a.request_id=b.request_id
--and a.REQUESTOR not in ('SYSADMIN')
ORDER BY a.request_id DESC

Assigning Responsibility using FND_USER_PKG

BEGIN
fnd_user_pkg.addresp
(username => UPPER
('MUQTHIYAR.PASHA'),
resp_app => 'FND',
resp_key => 'APPLICATION_DEVELOPER',
security_group => 'STANDARD',
description => NULL,
start_date => SYSDATE,
end_date => NULL
);
COMMIT;
DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Responsibility is not added due to'|| SQLCODE|| SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;
/



/* Assign Rest to user0) */
BEGIN
    FOR r
        IN (SELECT distinct fu.user_name,
       fa.application_short_name,
       fr.responsibility_key,   
       furg.start_date,           
       furg.end_date
  FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND FU.USER_NAME IN ('USER1',
                                       'USER2',
                                       'DEVUSER1',
                                       'DEVUSER2',
                                       'FUNCUSER1',
                                       'FUNUSR2')
   AND frt.RESPONSIBILITY_NAME in ('Application Developer',
                            'Workflow Administrator',
                            'Functional Developer',
                            'Functional Administrator',
                            'XML Publisher Administrator',
                            'General Ledger KSA Ledger IT',
                            'General Ledger UAE Ledger IT',
                            'General Ledger FZE Ledger IT' ))
    LOOP
        BEGIN
            fnd_user_pkg.addresp (username         => UPPER (r.USER_NAME),
                                  resp_app         => r.APPLICATION_SHORT_NAME,
                                  resp_key         => r.RESPONSIBILITY_KEY,
                                  security_group   => 'STANDARD',
                                  description      => NULL,
                                  start_date       => SYSDATE,
                                  end_date         => NULL);
            COMMIT;
            DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line (
                       'Responsibility is not added due to'
                    || SQLCODE
                    || SUBSTR (SQLERRM, 1, 100));
                ROLLBACK;
        END;
    END LOOP;
END;
/


--
SELECT distinct fu.user_name,
       fa.application_short_name,
       fr.responsibility_key,   
       furg.start_date,           
       furg.end_date
  FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND FU.USER_NAME IN ('USER1',
                                       'USER2',
                                       'DEVUSER1',
                                       'DEVUSER2',
                                       'FUNCUSER1',
                                       'FUNUSR2')
   AND frt.RESPONSIBILITY_NAME in ('Application Developer',
                            'Workflow Administrator',
                            'Functional Developer',
                            'Functional Administrator',
                            'XML Publisher Administrator',
                            'General Ledger KSA Ledger IT',
                            'General Ledger UAE Ledger IT',
                            'General Ledger FZE Ledger IT')
   --AND UPPER(fu.user_name)      =  UPPER('USER1')  -- <change it>
   --AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
 --ORDER BY frt.responsibility_name;

Archive Log Statastics

select * from V$LOG_HISTORY

select * from V$ARCHIVE_DEST


select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;


select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;

select trunc(COMPLETION_TIME,'HH24') TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME,'HH24') order by 1;

SELECT TRUNC(COMPLETION_TIME) ARCHIVED_DATE,
           SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
      FROM V$ARCHIVED_LOG
     GROUP BY TRUNC(COMPLETION_TIME)
     ORDER BY 1;



select sum((blocks*block_size)/(1024*1024))SIZEinMB,sequence#,name
from v$archived_log
group by sequence#,name
order by sequence#


SELECT trunc(first_time) DAY,
     count(*) NB_SWITCHS,
     trunc(count(*)*log_size/1024) TOTAL_SIZE_KB,
     to_char(count(*)/24,'9999.9') AVG_SWITCHS_PER_HOUR
FROM v$loghist,(select avg(bytes) log_size from v$log)
GROUP BY trunc(first_time),log_size
/


-- Size of the archive log files each hour
alter session set nls_date_format = 'YYYY-MM-DD HH24';

SELECT TRUNC(COMPLETION_TIME, 'HH') ARCHIVED_DATE_HOUR,
            SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
       FROM V$ARCHIVED_LOG
      GROUP BY TRUNC(COMPLETION_TIME, 'HH')
      ORDER BY 1;


SELECT
            TRUNC(COMPLETION_TIME) ARCHIVED_DATE,
            THREAD#,
            SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
       FROM V$ARCHIVED_LOG
      GROUP BY TRUNC(COMPLETION_TIME), THREAD#
      ORDER BY 1, 2;
     
     
     

-- per day the volume in MBytes of archived logs generated
SELECT SUM_ARCH.DAY,
         SUM_ARCH.GENERATED_MB,
         SUM_ARCH_DEL.DELETED_MB,
         SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      GENERATED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      DELETED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');


-- display the number of archived logs generated per hour per day:

---number of archived logs generated per hour per day

SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL))
            "00-01",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '01', 1, NULL))
            "01-02",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '02', 1, NULL))
            "02-03",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '03', 1, NULL))
            "03-04",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '04', 1, NULL))
            "04-05",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '05', 1, NULL))
            "05-06",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '06', 1, NULL))
            "06-07",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '07', 1, NULL))
            "07-08",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '08', 1, NULL))
            "08-09",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '09', 1, NULL))
            "09-10",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '10', 1, NULL))
            "10-11",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '11', 1, NULL))
            "11-12",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '12', 1, NULL))
            "12-13",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '13', 1, NULL))
            "13-14",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '14', 1, NULL))
            "14-15",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '15', 1, NULL))
            "15-16",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '16', 1, NULL))
            "16-17",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '17', 1, NULL))
            "17-18",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '18', 1, NULL))
            "18-19",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '19', 1, NULL))
            "19-20",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '20', 1, NULL))
            "20-21",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '21', 1, NULL))
            "21-22",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '22', 1, NULL))
            "22-23",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '23', 1, NULL))
            "23-00",
         COUNT (*) TOTAL
    FROM V$ARCHIVED_LOG
WHERE ARCHIVED='YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');



-- Combination of these scripts is:

SELECT LOG_HISTORY.*,
         SUM_ARCH.GENERATED_MB,
         SUM_ARCH_DEL.DELETED_MB,
         SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL))
                      "00-01",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '01', 1, NULL))
                      "01-02",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '02', 1, NULL))
                      "02-03",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '03', 1, NULL))
                      "03-04",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '04', 1, NULL))
                      "04-05",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '05', 1, NULL))
                      "05-06",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '06', 1, NULL))
                      "06-07",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '07', 1, NULL))
                      "07-08",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '08', 1, NULL))
                      "08-09",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '09', 1, NULL))
                      "09-10",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '10', 1, NULL))
                      "10-11",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '11', 1, NULL))
                      "11-12",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '12', 1, NULL))
                      "12-13",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '13', 1, NULL))
                      "13-14",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '14', 1, NULL))
                      "14-15",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '15', 1, NULL))
                      "15-16",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '16', 1, NULL))
                      "16-17",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '17', 1, NULL))
                      "17-18",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '18', 1, NULL))
                      "18-19",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '19', 1, NULL))
                      "19-20",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '20', 1, NULL))
                      "20-21",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '21', 1, NULL))
                      "21-22",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '22', 1, NULL))
                      "22-23",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '23', 1, NULL))
                      "23-00",
                   COUNT (*) TOTAL
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) LOG_HISTORY,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      GENERATED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      DELETED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE LOG_HISTORY.DAY = SUM_ARCH.DAY AND SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (LOG_HISTORY.DAY, 'DD/MM/YYYY');

Application Check List

Application Information Gathering
=================================

Existing Operating Units
========================

select ORGANIZATION_ID, NAME
from hr_operating_units
order by ORGANIZATION_ID;


Registered Applications
=======================

select application_id, application_short_name, basepath
from fnd_application
order by application_id;

Registered ORACLE Schemas
=========================

select fou.oracle_id, fou.oracle_username
        , fou.install_group_num, fou.read_only_flag
        , decode(nvl(du.username,' X '),' X ','No','Yes') in_dba_users
        , nvl(du.default_tablespace,'Unknown') default_tablespace
        , nvl(du.temporary_tablespace,'Unknown') temporary_tablespace
from dba_users du, fnd_oracle_userid fou
where du.username(+) = fou.oracle_username
order by 1

Product Installation Status, Version Info and Patch Level
=========================================================

select decode(nvl(a.APPLICATION_short_name,'Not Found'),
        'SQLAP','AP','SQLGL','GL','OFA','FA',
        'Not Found','id '||to_char(fpi.application_id),
        a.APPLICATION_short_name) apps,
        decode(fpi.status,'I','Installed','S','Shared',
               'N','Inactive',fpi.status) status,
        fpi.product_version,
        nvl(fpi.patch_level,'-- Not Available --') Patchset,
        to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
  and fpi.oracle_id = o.oracle_id(+)
order by 1,2;


 Product Database Configuration
 ==============================

select decode(nvl(a.APPLICATION_short_name,'Not Found'),
        'SQLAP','AP','SQLGL','GL','OFA','FA',
        'Not Found','id '||to_char(fpi.application_id),
        a.APPLICATION_short_name) apps,
        decode(nvl(o.ORACLE_username,'Not Found'),
               'Not Found','id '||to_char(fpi.oracle_id),
               o.ORACLE_username) ORACLE_username,
        decode(fpi.db_status,'I','Installed','L','Custom','N','Not Installed',
        'S','Shared',fpi.db_status) "DB Status",
        fpi.install_group_num,
        fpi.sizing_factor,
        fpi.tablespace, fpi.index_tablespace
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
  and fpi.oracle_id = o.oracle_id(+)
order by 1,2

Adding Report to Request Group using API

/**********************************************************************
*PURPOSE: To Add a Concurrent Program to a Request Group from backend *                                 
**********************************************************************/
--

DECLARE
    l_program_short_name    VARCHAR2 (200);
    l_program_application   VARCHAR2 (200);
    l_request_group         VARCHAR2 (200);
    l_group_application     VARCHAR2 (200);
    l_check                 VARCHAR2 (2);
--
BEGIN
    --
    l_program_short_name := 'XXINVNLCDNT';
    l_program_application := 'Inventory';
    l_request_group := 'OM Concurrent Programs';
    l_group_application := 'Order Management';
    --
    --Calling API to assign concurrent program to a reqest group
    --
    apps.fnd_program.add_to_group (
        program_short_name    => l_program_short_name,
        program_application   => l_program_application,
        request_group         => l_request_group,
        group_application     => l_group_application);
    --
    COMMIT;

    --
    BEGIN
        --
        --To check whether a paramter is assigned to a Concurrent Program or not
        --
        SELECT 'Y'
          INTO l_check
          FROM fnd_request_groups       frg,
               fnd_request_group_units  frgu,
               fnd_concurrent_programs  fcp
         WHERE     frg.request_group_id = frgu.request_group_id
               AND frg.application_id = frgu.application_id
               AND frgu.request_unit_id = fcp.concurrent_program_id
               AND frgu.unit_application_id = fcp.application_id
               AND fcp.concurrent_program_name = 'XXINVNLCDNT';

        --
        DBMS_OUTPUT.put_line (
            'Adding Concurrent Program to Request Group Succeeded');
    --
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            DBMS_OUTPUT.put_line (
                'Adding Concurrent Program to Request Group Failed');
    END;
END;

FNDCPASS Error Java system class reported: release of classes.bin in the database does not match that of the oracle executable - USER ( APPS ) has been detected in FND_WEB_SEC.GET_OP_VALUE

FNDCPASS system/***** SYSTEM APPLSYS *****

+----------------------------------------------------------------------------+
Working...
Oracle error -29548: ORA-29548: Java system class reported: release of classes.bin in the database does not match that of the oracle executable - USER ( APPS ) has been detected in FND_WEB_SEC.GET_OP_VALUE.

1. Create and run the following SQL script from a new SQLPLUS session
-- Start of File rmcorejvm.sql
connect / as sysdba
spool rmcorejvm.log
set echo on
set serveroutput on
select owner, status, count(*) from all_objects
   where object_type like '%JAVA%' group by owner, status;
execute rmjvm.run(false);
shutdown immediate
set echo off
spool off
exit
-- End of File rmcorejvm.sql


2. Review the log for any errors that cannot be explained by the action taken

 3. If the above SQL script completed successfully, then create and run the following SQL script from a new SQLPLUS session.
-- Start of File corejvminst.sql
connect / as sysdba
spool corejvminst.log
set serveroutput on
set echo on
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
select owner, status, count(*) from all_objects
    where object_type like '%JAVA%' group by owner, status;
create or replace java system
/
shutdown immediate
set echo off
spool off
exit
-- End of File corejvminst.sql

4. Restart the database and resolve any invalid java objects
connect / as sysdba
startup
select owner, status, count(*) from all_objects where object_type like '%JAVA%' group by owner, status;

If invalid(s) exists, then execute:
@?/rdbms/admin/utlrp.sql

Followed by a check for Invalid(s):
select owner, status, count(*) from all_objects where object_type like '%JAVA%' group by owner, status;

The core JVM objects should now be fully re-installed. The count of Java objects owned by the SYS user should be the same as before the re-installation.


How to Perform a Non-Default Oracle JVM Re-installation (Doc ID 429787.1)