Query Time Out

Posted on 2011-10-04
Last Modified: 2013-01-14
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 pls advice how to acheive this
Question by:Kanigi
    LVL 24

    Expert Comment

    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:
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.
    LVL 34

    Expert Comment

    "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?

    Author Comment


    Yes its affecting at the session 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
    LVL 34

    Accepted Solution

    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
        IDLE_TIME 3
        CONNECT_TIME 5;

    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;

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now