Search code examples
databaseoracle-databasedatabase-designdatabase-administrationdatabase-locking

Tables oracle locked

I´m not a dba but I´m having a problems with a database which it´s been locking tables and creating a big chaos as a result. I get some information from the oracle dba, if someone could help me to found the key of the problem or point me what I need to do, I put more information here:

enter image description here

enter image description here

I have a big report from oracle but I don´t understand 95% of the data.

Solution

  • This looks like regular row-level locking. A row can only be modified by one user at a time. The data dictionary has information about who is blocked and who is doing the blocking:

    --Who's blocking who?
select
    blocked_sql.sql_id blocked_sql_id
    ,blocked_sql.sql_text blocked_sql_text
    ,blocked_session.username blocked_username
    ,blocking_sql.sql_id blocking_sql_id
    ,blocking_sql.sql_text blocking_sql_text
    ,blocking_session.username blocking_username
from gv$sql blocked_sql
join gv$session blocked_session
    on blocked_sql.sql_id = blocked_session.sql_id
    and blocked_sql.users_executing > 0
join gv$session blocking_session
    on blocked_session.final_blocking_session = blocking_session.sid
    and blocked_session.final_blocking_instance = blocking_session.inst_id
left join gv$sql blocking_sql
    on blocking_session.sql_id = blocking_sql.sql_id;

    If you understand the system it is usually easier to focus on "who" is doing the blocking instead of "what" is blocked. The above query only returns a few common columns, but there are dozens of other columns in those tables that might help identify the process.