Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Sunny.

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

Answered by: Connor McDonald - Last updated: June 18, 2020 - 1:34 pm UTC

Category: Database Administration - Version: 12c

Viewed 100+ 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 we 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.

and you rated our response

  (3 ratings)

Reviews

Check commit or rollback

June 10, 2020 - 10:59 am UTC

Reviewer: Suuny

Can we check commit or rollback issue by "session 1" using system event ?
Connor McDonald

Followup  

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

June 17, 2020 - 11:09 am UTC

Reviewer: A reader

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

Followup  

June 17, 2020 - 2:33 pm UTC

I'd need to see some proof of that

June 18, 2020 - 11:24 am UTC

Reviewer: A reader

Can you give a what kind of information you required so i collect it for further investigate?
Connor McDonald

Followup  

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