Oracle Apps DBA

Thursday, 7 September 2017

ORA-20005 WHEN GATHERING STATISTICS FOR EBS APPS 11i or R12 IN Database 10G or 11G (Doc ID 375351.1)

To Bottom

In this Document

APPLIES TO:

Oracle EBS Applications Performance - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.
10g or 11g Database with Apps 11i or R12

SYMPTOMS

Gathering statistics using concurrent jobs "Gather Schema Statistics / Gather Table Statistics" or running FND_STATS fails with "ORA-20005: object statistics are locked".
Example 1:

Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=AR.AR_REV_REC_QT***ORA-20005: object statistics are locked (stattype = ALL)***

Example 2:
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP stats on table GAT_REQ_QTBL is locked

CAUSE

This can happen with Advance Queue tables. In 10g, if a queue is created or recreated during the upgrade, automatic statistics gather is locked (disabled) on these queue.
The following statement can be used to check the tables which have statistics locked:

    select owner, table_name, stattype_locked

    from dba_tab_statistics

    where stattype_locked is not null; 

SOLUTION

Unlock statistics gathering on those queues running the commands below. To unlock all the tables in a schema at once:

exec dbms_stats.unlock_schema_stats('schema_owner'); 

OR
To unlock individual tables (need to run for all tables individually):

exec dbms_stats.unlock_table_stats('table_owner','table_name');

Examples:

SQL> exec dbms_stats.unlock_schema_stats ('AR');

SQL> exec dbms_stats.unlock_table_stats('AR', 'AR_REV_REC_QT'); 

Gather Schema Statistics fails with Ora-20001 errors after 11G database Upgrade (Doc ID 781813.1)

11i - 12 Gather Schema Statistics fails with Ora-20001 errors after 11G database Upgrade (Doc ID 781813.1)

In this Document

APPLIES TO:

Oracle Applications Technology Stack - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Oracle EBS Applications Performance - Version 12.1.1 to 12.1.1 [Release 12.1]
Oracle Applications DBA - Version 11.5.10.2 to 11.5.10.2 [Release 11.5.10]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 11.1.0.6 to 11.1.0.8

SYMPTOMS

"Gather Schema Statistics" program reported following errors in request log files:

Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #4: ERROR: While GATHER_TABLE_STATS: object_name=FII.FII_FIN_ITEM_HIERARCHIES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

CHANGES

Problem started after database has been upgraded to 11G.

CAUSE

There are two reasons for that error message:
1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.
Because of this problem, FND_STATS tries to gather histogram information using wrong command and
it fails with ora-20001 errors.

The following SQL should return one row, not two:

SQL>select column_name, nvl(hsize,254) hsize

from FND_HISTOGRAM_COLS

where table_name = 'JE_BE_LINE_TYPE_MAP'

order by column_name;

COLUMN_NAME HSIZE

------------------------------ ----------

SOURCE 254

SOURCE 254

Since there are two rows in histograms table, FND_STATS creates following command to gather statistics on table 'JE_BE_LINE_TYPE_MAP' :

 dbms_Stats.gather_table_stats(OWNNAME => 'GL', TABNAME

=>'JE_BE_LINE_TYPE_MAP',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SOURCE SIZE 254 FOR

COLUMNS SOURCE SIZE 254');

Above command will work on 9i and 10G databases but it will fail with ora-20001 errors on 11G.

2) Column does not exist on the table but still listed in FND_HISTOGRAMS_COL table.
You can use following SQL to identify. SQL will prompt for table name, use table name from the errors. In above examples you can use FII_FIN_ITEM_HIERARCHIES.

select hc.table_name, hc.column_name

from FND_HISTOGRAM_COLS hc , dba_tab_columns tc

where hc.table_name ='&TABLE_NAME'

and hc.table_name= tc.table_name (+)

and hc.column_name = tc.column_name (+)

and tc.column_name is null;

SOLUTION

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them logged in as the applsys user.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

-- identify duplicate rows

select table_name, column_name, count(*)

from FND_HISTOGRAM_COLS

group by table_name, column_name

having count(*) > 1;

-- Use above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS

where table_name = '&TABLE_NAME'

and  column_name = '&COLUMN_NAME'

and rownum=1;

-- Use following SQL to delete obsoleted rows

delete from FND_HISTOGRAM_COLS

where (table_name, column_name) in

  (

   select hc.table_name, hc.column_name

   from FND_HISTOGRAM_COLS hc , dba_tab_columns tc

   where hc.table_name  ='&TABLE_NAME'

   and hc.table_name= tc.table_name (+)

   and hc.column_name = tc.column_name (+)

   and tc.column_name is null

  );

commit;

Query to find responsibilities for particular user

select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = upper('&user_name') and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id and fr.language = userenv('LANG');

Query to find all responsibilities of a user

-------------------------------------------------------------------------------

-- Query to find all responsibilities of a user

-------------------------------------------------------------------------------

SELECT fu.user_name "User Name",

frt.responsibility_name "Responsibility Name",

furg.start_date "Start Date",

furg.end_date "End Date",

fr.responsibility_key "Responsibility Key",

fa.application_short_name "Application Short Name"

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 UPPER(fu.user_name) = UPPER('AMOHSIN') -- <change it>

-- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))

ORDER BY frt.responsibility_name;

Thursday, 31 August 2017

Oracle Data Pump

This chapter gives the original Export and Import utilities a primer on how to move up to the faster, more powerful, and more flexible Data Pump Export and Import utilities.

In this Chapter I going to export and import SALES schema

Export Schema

Step 1 : Login to Source Database server

login as: orapreprod

password:******

$ cd /u03/CRP02/db/tech_st/12.1.0/

$ . CRP2_dev.env

$ sqlplus "/as sysdba"

Step 2 : Connect as system user

SQL> conn system/clonemanager
Connected.

SQL> desc all_directories;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
DIRECTORY_NAME NOT NULL VARCHAR2(128)
DIRECTORY_PATH VARCHAR2(4000)
ORIGIN_CON_ID NUMBER

Step 4 : Creating Directory Objects If doesn’t exist

If You Want to create New DATAPUMP_DIR DIRECTORY

SQL> CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u03/CRP02/DATA_PUMP_DIR';

SQL> select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from ALL_DIRECTORIES;

SYS
DATA_PUMP_DIR
/u03/CRP02/DATA_PUMP_DIR

$ cd /u03/CRP02/

$ mkdir DATA_PUMP_DIR

$ cd /u03/CRP02/DATA_PUMP_DIR/

$ pwd
/u03/CRP02/DATA_PUMP_DIR

$ cd /u03/CRP02/db/tech_st/12.1.0/

$ . CRP2_dev.env

Step 5 : Export shema from source database

$ expdp system/clonemanager schemas=SALES directory=DATA_PUMP_DIR dumpfile=30AUG17_SALES_SCHEMA.dmp logfile=30AUG17_SALES_SCHEMA.log

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/CRP02/DATA_PUMP_DIR/30AUG17_SALES_SCHEMA.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 30 16:17:58 2017 elapsed 0 00:15:11

$ cd /u03/CRP02/DATA_PUMP_DIR/

$ ls -lrt
-rw-r--r-- 1 orapreprod dba 41016 Aug 30 16:17 30AUG17_SALES_SCHEMA.log
-rw-r----- 1 orapreprod dba 493174784 Aug 30 16:17 30AUG17_SALES_SCHEMA.dmp

Step 6 : Copy Exported dumpfile to target database

$ scp -pr 30AUG17_SALES_SCHEMA.dmp orauat@172.25.4.50:/u01/UAT/DATA_PUMP_DIR

The authenticity of host '172.25.4.50 (172.25.4.50)' can't be established.
RSA key fingerprint is 49:81:30:ed:2c:17:5c:79:ee:30:79:2d:15:b0:f3:9e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.25.4.50' (RSA) to the list of known hosts.
orauat@172.25.4.50's password:
30AUG17_SALES_SCHEMA.dmp 100% 470MB 156.8MB/s 00:03

Import Schema

Step 1 : Login to Target database server

login as : orauat

password : ******

$ cd /u01/UAT/db/tech_st/12.1.0

$ . UAT_ebsuatdb.env

$ sqlplus "/as sysdba"

$ cd /u01/UAT

Step 2 : Creating Directory Objects If doesnot exist

$ mkdir DATA_PUMP_DIR

SQL> CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u01/UAT/DATA_PUMP_DIR';

$ cd DATA_PUMP_DIR/

$ pwd
/u01/UAT/DATA_PUMP_DIR

Step 3 : Backup SCHEMA if already exist target database

$ expdp system/clonemanager schemas=SALES directory=DATA_PUMP_DIR dumpfile=30AUG17_SALES_SCHEMA_BACKUP.dmp logfile=30AUG17_SALES_SCHEMA_BACKUP.log

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/UAT/DATA_PUMP_DIR/30AUG17_SALES_SCHEMA_BACKUP.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 30 16:04:49 2017 elapsed 0 00:14:20

$ ls -lrt
-rw-r-----. 1 orauat dba 35319808 Aug 30 16:04 30AUG17_SALES_SCHEMA_BACKUP.dmp
-rw-r--r--. 1 orauat dba 33531 Aug 30 16:04 30AUG17_SALES_SCHEMA_BACKUP.log

$ pwd
/u01/UAT/DATA_PUMP_DIR

Step 4 : Drop Existing Schema

Before drop user connect and check the DEFAULT TABLESPACE and TEMPORARY TABLESPACE of particular schema.

SQL> select * from DBA_USERS where username='SALES';

$ sqlplus "/as sysdba"

SQL> drop user sales cascade;
drop user sales cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

-----------------------------------------------------------------------------------------------------------------

Currently SALES User connected
So disconnect the SALES user by below steps

Kill connected user session
---------------------------------
SQL > select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
-- b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;

$ kill -9 26713

$ sqlplus "/as sysdba"

SQL> drop user sales cascade;

User dropped.

Step 5 : Create Schema

SQL>CREATE USER SALES IDENTIFIED BY sales DEFAULT TABLESPACE APPS_POS_DATA TEMPORARY TABLESPACE TEMP1 PROFILE DEFAULT ACCOUNT UNLOCK;

$ ls -lrt

-rw-r-----. 1 orauat dba 35319808 Aug 30 16:04 30AUG17_SALES_SCHEMA_BACKUP.dmp
-rw-r--r--. 1 orauat dba 33531 Aug 30 16:04 30AUG17_SALES_SCHEMA_BACKUP.log
-rw-r-----. 1 orauat dba 493174784 Aug 30 16:17 30AUG17_SALES_SCHEMA.dmp

Step 6: Now Import the schema from source dump file.

Copy the source dump file to target database.
And run the below command

$ impdp system/clonemanager schemas=SALES directory=DATA_PUMP_DIR dumpfile=30AUG17_SALES_SCHEMA.dmp logfile=30AUG17_SALES_SCHEMA.log remap_schema=SALES:SALES

Error ORA-20100 .tmp Creation For FND_FILE Failed

APPLPTMP - this parameter directly update from $CONTEXT_FILE for usr tmp location issue

Difference between APPLPTMP and APPLTMP Directories in EBS (Doc ID 1355735.1)

Error ORA-20100 .tmp Creation For FND_FILE Failed In FA Concurrent Requests (Doc ID 392635.1)

Issue

**Starts**18-AUG-2017 11:26:54

**SQL error and free**18-AUG-2017 11:27:14

ORACLE error 20100 in FDPSTP

Cause: FDPSTP failed due to ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.

Directory &FILE_DIR is invalid, contact your system administrator.

Action: Check that the directory is a valid directory with wri18-AUG-2017 11:27:14

While i checked cd $APPLPTMP parameter in $CONTEXT_FILE

it shows some different location instead of my current server location

Solution

1.shut down application tier

2.So i have update $CONTEXT_FILE with below /usr/tmp

grep APPLPTMP $CONTEXT_FILE

3.run autoconfig application tier

Saturday, 19 August 2017

OERR: ORA-28040 "No matching authentication protocol" Reference Note (Doc ID 288402.1)

PURPOSE

This is a brief reference note to show the meaning of database error "ORA-28040". It includes the error text, "Cause" and "Action" from message files for each database version from 9.2 onwards, along with search links and details of any database bug issues related to the error.

SCOPE

This note is intended for general audience as a starting point to check the meaning of "ORA-28040". It does not give detailed information about the error and does not give error message text for versions below 9.2 .

DETAILS

Error Text, Cause and Action from Message File/s for ORA-28040

Versions 12.1

Error:  ORA-28040 No matching authentication protocol 
---------------------------------------------------------------------------
Cause:  There was no acceptable authentication protocol for either client or 
 server. 
Action: The administrator should set the values of the 
 SQLNET.ALLOWED_LOGON_VERSION_SERVER and 
 SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters, on both the client and 
 on the server, to values that match the minimum version software 
 supported in the system. This error is also raised when the client is 
 authenticating to a user account which was created without a verifier 
 suitable for the client software version. In this situation, that 
 account's password must be reset, in order for the required verifier to 
 be generated and allow authentication to proceed successfully.

Versions 10.1, 10.2, 11.1, 11.2

Error:  ORA-28040 No matching authentication protocol 
---------------------------------------------------------------------------
Cause:  No acceptible authentication protocol for both client and server 
Action: Administrator should set SQLNET_ALLOWED_LOGON_VERSION parameter on 
 both client and servers to values that matches the minimum version 
 supported in the system.


1.Go to Database server

2.source database environment file

3.cd $TNS_ADMIN

4.vi sqlnet.ora

5.add below line



SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

FDPSTP failed due to ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed

APPLPTMP - this parameter directly update from $CONTEXT_FILE for usr tmp location issue

Difference between APPLPTMP and APPLTMP Directories in EBS (Doc ID 1355735.1)

Error ORA-20100 .tmp Creation For FND_FILE Failed In FA Concurrent Requests (Doc ID 392635.1)

Issue

**Starts**18-AUG-2017 11:26:54

**SQL error and free**18-AUG-2017 11:27:14

ORACLE error 20100 in FDPSTP

Cause: FDPSTP failed due to ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.

Directory &FILE_DIR is invalid, contact your system administrator.

Action: Check that the directory is a valid directory with wri18-AUG-2017 11:27:14

While i checked cd $APPLPTMP parameter in $CONTEXT_FILE

it shows some different location instead of my current server location

Solution

1.shut down application tier

2.update $CONTEXT_FILE with "/usr/tmp"

grep APPLPTMP $CONTEXT_FILE

3.run autoconfig in application tier

Wednesday, 16 August 2017

Apps user account locked solution

R12: Clone fails with error "ORA-28000 The account is locked" (Doc ID 1114244.1)

SYMPTOMS

The clone of an E-Business Suite Instance of Release 12 fails with following error:

...
Exception occurred: java.sql.SQLException: ORA-28000: the account is locked
ORA-28000: the account is locked
...

CAUSE

The issue is caused by the APPS Database account, which is locked.

The following script allows you to check the number of failed logins, which can lock the apps user :

select profile , resource_name, limit
from dba_profiles
where resource_name ='FAILED_LOGIN_ATTEMPTS';

Example output :

DEFAULT FAILED_LOGIN_ATTEMPTS : 10
AD_PATCH_MONITOR_PROFILE FAILED_LOGIN_ATTEMPTS : 3

For additional information, please review Note 114930.1 - 'Oracle Password Management Policy'.

SOLUTION

To implement the solution, please test the following steps in a Development instance and migrate accordingly:

1. Ensure that you have taken a backup of your Environment.

2. Increase the number of failed login which locks apps user (optional):

alter profile AD_PATCH_MONITOR_PROFILE limit failed_login_attempts unlimited;
alter profile default limit failed_login_attempts unlimited password_lock_time 1/1440;

3. Unlock the locked APPS User account :

alter user apps account unlock;

4. Rerun the failed cloning commands.

5. Migrate the solution as appropriate to other Environments.

Wednesday, 26 July 2017

TABLESPACE And Temporary Tablespace Analyse

select file_name,bytes/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='APPS_TS_TX_DATA';

--ALTER DATABASE DATAFILE '/kzn01/TEST/db/apps_st/data/a_txn_data06.dbf' RESIZE 2G;

----------------------------------------
select * from dba_data_files where TABLESPACE_NAME='APPS_TS_TX_IDX';

ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '/kzn03/Oracle/PROD/db/apps_st/data/a_txn_ind07.dbf' SIZE 2G;

---------------------------------------
list tablespaces, size, free space, and percent free
 
SELECT df.tablespace_name TABLESPACE, 
df.total_space_mb TOTAL_SPACE_GB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_GB, fs.free_space_mb FREE_SPACE_GB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) TOTAL_SPACE_MB
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) FREE_SPACE_MB
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+) ORDER BY fs.tablespace_name;



--------------------------------------------------
Full database size

SELECT ROUND (SUM (used.bytes) / 1024 / 1024 / 1024) || ' GB'
"Database Size",ROUND (SUM (used.bytes) / 1024 / 1024 / 1024)
- ROUND (free.p / 1024 / 1024 / 1024)|| ' GB' "Used space",
ROUND (free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
FROM (SELECT   bytes FROM v$datafile UNION ALL
SELECT bytes FROM v$tempfile UNION ALL SELECT bytes FROM v$log) used,
(SELECT SUM (bytes) AS p FROM dba_free_space)free GROUP BY free.p;

============================================================================

Master Note: Overview of Oracle Temporary Tablespaces (Doc ID 1498442.1)

SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM database_properties
WHERE property_name='DEFAULT_TEMP_TABLESPACE';

SELECT group_name, tablespace_name FROM dba_tablespace_groups;
SELECT * FROM dba_tablespace_groups;

SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM database_properties
WHERE property_name='DEFAULT_TEMP_TABLESPACE';


SELECT * FROM dba_tablespace_groups;

SELECT TABLESPACE_NAME,free_space/1024/1024/1024 from DBA_TEMP_FREE_SPACE;

SELECT * from DBA_TEMP_FREE_SPACE;

SELECT file#, name, bytes/1024/1024 mb FROM v$tempfile;

SELECT * FROM v$tempfile;

 
 
-- /u03/CRP1GST/db/apps_st/data/temp02.dbf
 
--ALTER DATABASE TEMPFILE '/u01/UAT/db/apps_st/data/temp03.dbf' RESIZE 2G;
 --ALTER TABLESPACE TEMP2 ADD TEMPFILE '/u01/app/oracle/tempfile/tempo3.dbf' size 1000M reuse;
 commit;

TBALESPACE CHECK

 
 SELECT df.tablespace_name TABLESPACE, 
df.total_space_mb TOTAL_SPACE_GB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_GB, fs.free_space_mb FREE_SPACE_GB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) TOTAL_SPACE_MB
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) FREE_SPACE_MB
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+) ORDER BY fs.tablespace_name;

Thursday, 20 July 2017

While Apply GST patch 19287293 Locked object

1.

select c.owner,c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;

OutPut

APPLSYS FND_CONCURRENT_QUEUES TABLE 417 62192 INACTIVE orapreprod dev.tbzoriginal.local
APPLSYS FND_CONCURRENT_REQUESTS TABLE 418 46553 INACTIVE orapreprod dev.tbzoriginal.local

---------------------------------------------------------

Solution
----------

Hi,

Please execute the following:

ALTER SYSTEM KILL SESSION '417,62192';
ALTER SYSTEM KILL SESSION '418,46553';

Check if the 2 locks have been released:

select c.owner,c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;

Retry the patching.