Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, vishnudas.

Asked: May 02, 2018 - 9:19 am UTC

Last updated: May 02, 2018 - 10:02 am UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

hi there i have simple update command

UPDATE USERMAST SET USER_LOCK='N' WHERE USER_NAME='rancf';


when i execute this query it keeps on running..

USER_ID is the pk for USERMAST table and i tried with USER_ID TOO,same thing is happening
BUT
When i have tried with other USER_NAME,those all are working perfectly.

What could be the problem???? is there 'BLOCK' OR 'LOCK' is happening??

please help me to resolve this...

and Chris said...

Either:

- Your update is blocked by another session
- The update takes a long time to run!

For details on finding blockers, read:

http://www.orafaq.com/node/854

If it's not that, trace your session to see what's going. Even if the update changes one row, there may be triggers doing lots of unexpected work. Read more about tracing at:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof

PS: if you regularly have to update the primary key, you're doing it wrong! Add a (sequence assigned) surrogate key instead. And place a unique constraint over USER_NAME.

Rating

  (1 rating)

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

Comments

vishnudas, May 02, 2018 - 1:42 pm UTC

thank you for your help :)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.