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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.