For our application, we have recently faced database blocking two times and application was not responding properly.
The DBA killed those blocking sessions and it resolved the issue. Now, we are trying to create a database script to give us alert mail incase of any blocking exists in database.
The script has been created using v$lock to get the blocking sessions information but while testing it, the script shows many false alert. It results few sessions which are actually not blocking the database working or tables. These sessions are related to PRLOCK, TEMP_WBS tables.
Is anyone has created such kind of script or help us to identify true blocking sessions in the database?