Query Time Out

HI ,

I need to set an Query Time out option in my Oracle Data Base Server which  we need to restrict the queries which is running more than 2 mins.So we tried with some of the options but it is giving the effect at the session level.so pls advice how to acheive this
Who is Participating?
Mark GeerlingsDatabase AdministratorCommented:
You can use profiles for this, but no, it is not the CONNECT_TIME value that you need to limit.  (You could use that if you wanted to automatically log users out after they have been connected for more than eight hours, for example.)

Oracle profiles do not provide a value like QUERY_MINUTES or QUERY_SECONDS.  You will have to use: LOGICAL_READS_PER_CALL.  This is the number of block reads the query is allowed to do.  If it tries to do more than this, it will be cancelled and the user will get no resutls.  They will just see an "ORA-02395 - exceeded call limit on I/O usage" error.

Here is the command we used in one of our systems to set some profile limits:

alter profile default
  limit sessions_per_user       6
        cpu_per_session         240000
        cpu_per_call            3000
        connect_time            600
        logical_reads_per_session 2000000
        logical_reads_per_call  40000;

Be careful with this though.  Most likely all of your users now share this default profile.  So, this could create more problems that it solves.

It may be safer to leave the default profile unchanged, and use something like this instead:
    CPU_PER_SESSION 240000
    CPU_PER_CALL 6000

Then you can do an "alter user..." command for each user that you want to be limited to this profile, for example:
alter user problem_child profile short_test;
I don't know exactly how it is done but this thread discusses the use of Resource Manager for exactly the same requirements as yours:


The 11g documentation chapter on Resource Manager:

slightwv (䄆 Netminder) Commented:
I'm not sure a resource plan is necessary for this.  I would set up a profile (mentioned in the dbasupport link as well).  I'm thinking the CONNECT_TIME parameter is what you need.
Mark GeerlingsDatabase AdministratorCommented:
"but it is giving the effect at the session level"  How is that a problem?  What level do you expect this limit to be applied at?

When you say: "we need to restrict the queries which [are] running more than 2 mins", how exactly do you want them to be restricted?  That is, what action do you want Oracle to do if/when this happens?
KanigiAuthor Commented:

Yes its affecting at the session level.so i need to get it done for an query level like if an query is running mor ethan 2 mins it should throe an error
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.