Are you receiving this when trying to add any server property?
Looking at this UPDATE_PROP_DEF_VAL procedure, it seems like it updates the following tables:
Could you ask your DBA to check to see if these tables are locked by something? If so, perhaps he/she can tell us what query is locking it?
Do you have BL Cleanup running at this time?
Yes, I am getting this error when trying to add a server property in property dictionary.
BL Cleanup jobs are running in a different time than when i tried creating the server property.
I have asked my DBA to check those two tables if they were locked by something and waiting for their response.
We could see lot of sessions on those two tables.
select distinct count(*) from v$access where object in ('PROP_SET_CLASS_PROP_DEF_VAL') and type in ('TABLE');
select distinct count(*) from v$access where object in ('PROP_SET_INSTANCE_PROP_VAL') and type in ('TABLE');
When the deadlock occurs, is there any more information available on the DB side logs about the nature of the deadlock? Which table was locked, what had it locked and for how long etc etc.
I know you are working with Chetan on this and I will also ask him to request an export of Appserver Details from this env – this might shed some light on some of the DB connections in use etc.