Oracle connection/query timeout

问题:

Is it possible to specify connection/query timeout for the Oracle database queries? Either on Oracle side or in Oracle's JDBC driver (10.2.0.4)? So, that Java client just got an error back after, let's say, 2 minutes instead of waiting until Oracle finishes executing the query?

回答1:

If you are executing the query in the context of a transaction, the transaction timeout value of the JTA transaction monitor will be the determinant to query timeout. The configuration for this depends from one application server to another.

At an individual query level (in the absence of a JTA transaction monitor), the setQueryTimeout method can be used to set the timeout on the execution of a Statement/PreparedStatement/CallableStatement object.

Update

setQueryTimeout is not to be relied on, although it works (atleast from a J2SE client). It works via the JDBC driver performing a full round-trip to the Oracle database server. Then, it is upto the database to halt execution of the query. Don't rely on it for time critical applications.

回答2:

Have a look at Oracle profiles. This allows you to specify several limits at the database level. One of them is a maximum CPU time per query.

If you have queries running for more than 2 minutes on a regular basis you might want to do some tuning of your queries first.

回答3:

According to http://www.javamonamour.org/2012/09/oraclenetconnecttimeout.html

oracle.net.READ_TIMEOUT for jdbc versions < 10.1.0.5 oracle.jdbc.ReadTimeout for jdbc versions >=10.1.0.5

So if you are using a JDBC driver version 10.1.0.5 or higher, then oracle.jdbc.ReadTimeout is the correct property.