Refusing to run a query which will take too long? 2

[Custom24]

Hi
I've got an Intranet web app which has a page where people can enter custom SQL and bring back the results. Obviously the user under which the SQL runs is highly restricted so that they can't drop tables etc.
But they can still do crazy stuff like make a query which takes 57 years to run or something, and tie up the server resources
My question then is - for this user, is it possible to set something up so that Oracle will not continue executing queries if they take beyond a certain time, or else that Oracle will drop the connection if a query has been active for too long.
I'm using ASP.net, which keeps connections open using connection pooling, so ideally I'd like not just to timeout connections, but if this is the only way, then so be it.

Thanks


Mark

 

[carp]

You could set up profiles that limit things such as blocks read, cpu time, etc. However, profiles DO impose drag on your overall instance performance, so you'll need to weigh the tradeoffs (which is why we end up saying "it depends" so often!).

 

[Custom24]

Hi
Thanks for that. One of the options, CPU/Call, looked good. The problem is that we are having difficulty getting any of the profile options to make any difference.

Just as a test, we changed the Concurrent Sessions for the profile to 1, but sqlplus still lets us log in as many times as we want.

I am trying this on a machine running Windows 2000, with Oracle standard edition 8.1.7 I was using Oracle's DBA studio to make the profile changes.

Any thoughts?


Mark

 

[carp]

Did you set resouce_limit to TRUE?

Also, did you assign the profile to the user you are testing with?

I just ran a test on my own database and it limited me just fine.

 

[Custom24]

Carp
Thanks
We realised about resource_limit eventually from metalink, but thanks for pointing me in the right direction with the profiles.
Works like a charm now.
Mark

Mark