Issue/Symptom/Question

How do I find if there is a block and who is causing it?

Applies To

Database Admin 

System Database privileges

Resolution/Fix/Answer

Finding Database Block

The following SQL query will show you who is being blocked in the database.

select sid,serial#,osuser,client_info,machine,program,module,action,blocking_session 
from v$session 
where blocking_session in (select final_blocking_session from v$session where final_blocking_session is not null);

The following SQL query will show you who is causing the block in the database.

This user should be contacted to determine what they are doing in PENTA.  If they are not actively working in PENTA then you should consider killing their session

select sid,serial#,osuser,client_info,machine,program,module,action,sql_hash_value 
from v$session 
where sid in (select final_blocking_session from v$session where final_blocking_session is not null);

How to kill the Blocking Session

The query you run to show the session that is causing database blocking will output a "sid" and "serial#".  Those 2 values are used to kill the blocking database session.

Here are the steps to kill the session:

1. Log into the database as SYS ("as SYSDBA")
2. Run the following statement;

alter system kill session '<sid>,<serial#>';