is there a way to set the sql query time out on the database, if it is taking too long to process?
Client side? - Depends on the tool. Some tools have the capability to break.
Server side? - You could use a db monitoring tool to alert you to this problem. Then manually kill the query after assuring that it's OK.
Oracle 9i Resource Manager provides a new feature that facilitates the prevention of execution of operations that are estimated to run for a longer time than a predefined limit.
This can be accomplished by doing the following:
While creating a resource plan directive you need to specify the parameter
For example you can create a plan to specify a maximum execution time limit of 10 minutes by specifying the parameter in the following procedure
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (…,MAX_EST_EXEC_TIME =>600,……..);
Oracle calculates the time taken for a SQL statement based on statistics available and rejects the statement if the calculated estimated time is more than the set limit.
Hope this helps.
OCP 8i,9i DBA,SCJP, BEA WLS6CD
Click Here to Expand Forum to Full Width