Menu
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?
805
Using SQL Server Management StudioIn Object Explorer, right-click a server and select Properties. Click the Connections node. Under Remote server connections, in the Remote query timeout box, type or select a value from 0 through 2,147,483,647 to set the maximum number seconds for SQL Server to wait before timing out.
To avoid seeing error ORA-12170, use a static IP address rather than a DHCP for assigning an IP address of the host. In one case, the server may have shut down because the connection establishment or communication with a client did not complete in an allotted time interval.
You can set the maximum number of minutes after which Oracle Responsys logs out inactive users (i.e. session timeout). You can choose a session timeout of 15 minutes, 30 minutes, or 60 minutes.
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.
128
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.
30
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.
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
