CODEWITHSUNDEEP
oracle-databasesas
griboedov
griboedov

Reputation: 886

SAS connection to Oracle hung up for 2 hours

In SAS we have a library which is actually ORACLE schema and today I faced with a strange event when trying to query a table in this library. A regular SAS SQL query:

proc sql;
    delete from table where id=123;
quit;

Was hung up for two hours while it usually took some seconds:

NOTE: PROCEDURE      SQL used (Total process time):
      real time           2:00:33.49
      cpu time            0.03 seconds

While this operation was being performed I tried to delete a nearby row in ORACLE SQL DEVELOPER but it hung up processing delete request too. However deleting a row that was not nearby these rows did not cause any problems. Well how can I find out the possible reason? I guess that was a sort of deadlock.

Upvotes: 2

Views: 306

Answers (1)

Boneist
Boneist

Reputation: 23588

It sounds like someone has locked a row that your session is trying to delete. You should be able to spot this by querying v$session:

select sid, schemaname, osuser, terminal, program, event
from v$session
where type != 'BACKGROUND';

and checking if your session has an event of "enq: TX - row lock contention" (or similar). If so, then you'll have to work out who has the blocking lock (if you have access to Toad's session browser, this is easy to do, but Google should throw up something that can help. Or, if your database is Oracle 11.2, there's a view: v$session_blockers that ought to pinpoint the blocking session), and then get them to either commit or rollback their transaction.

Upvotes: 1

Related Questions