Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sunny.

Asked: May 27, 2020 - 8:22 am UTC

Last updated: June 18, 2020 - 1:34 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

I found some inactive session block the other session so what is the issue?

I also check sql_id of blocking session is null.
and Wait session is doing insert operation.

and Connor said...

An idle session can be a blocking session. For example

Session 1:

insert into my_table (pk) values (1);

(The column "pk" is the primary key column)

This session is now idle, because the insert has completed.

Session 2:

insert into my_table (pk) values (1);

will block, because it must wait for session 1 to commit or rollback to see if it is allowed to add the same primary key, or raise an error about duplicates.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Check commit or rollback

Suuny, June 10, 2020 - 10:59 am UTC

Can we check commit or rollback issue by "session 1" using system event ?
Connor McDonald
June 13, 2020 - 9:38 am UTC

If session 1 is blocking someone, then by definition it has not committed.

You can also check v$locked_object to see if a session has an open transaction

select statement

A reader, June 17, 2020 - 11:09 am UTC

Can normal select statement block another session?

Because I check many times my count(*) select query on just single table become blocker for another session.
Connor McDonald
June 17, 2020 - 2:33 pm UTC

I'd need to see some proof of that

A reader, June 18, 2020 - 11:24 am UTC

Can you give a what kind of information you required so i collect it for further investigate?
Connor McDonald
June 18, 2020 - 1:34 pm UTC

Look at v$locked_object, and look at the blocking_... columns in v$session

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database