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.

Answers


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.


Need Your Help

Why am I getting “run-time error '424': object required” using VBA in Excel?

excel vba excel-vba

I'm having an issue with some code I'm writing in VBA. I'm getting this error, but I don't think I'm using it wrong, since I wrote something very similar in another sub.

publishing WCF Service .NET 3.5 on IIS

wcf .net-3.5 iis-7 web-config was

I have a problem with publishing WCF Service on IIS. I think - the problem is in configuration(web.config).

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.