SET QUERY TIME OUT

[imose]

is there a way to set the sql query time out on the database, if it is taking too long to process?

[KenEwald]

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.

[ovidius]

What version, 8, 9?

[kolagani]

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

MAX_EST_EXEC_TIME

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.