Oracle – JDBC Validation Query

jdbcoracle

By default for Oracle Databases validation query is "select 1 from dual".
Tomcat says:

ValidationQuery – If specified, this query MUST be an SQL SELECT
statement that returns at least one row.

Based on sentences above, can I modify a validation query that will be:

nested or conditioned query
from another table I specify
this table has one row and multiple columns

I've read a lot of documentation, but didn't find any information to say that's impossible or at least incorrect.

Best Answer

Yes, it can be any valid query - from the Tomcat JDBC documentation:

(String) The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query does not have to return any data, it just can't throw a SQLException. The default value is null. If not specified, connections will be validation by the isValid() method. Example values are SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server)

You asked about using nested queries, different tables and multiple columns and so I give you something to think about:

The validationQuery is executed every time an application requests a connection from the pool. This is before the application issues it's own query - so at a minimum you issue two queries against the database. With that in mind, why would you want to make the query more complex? All you would do is add unnecessary load on the database server for no reason at all - AFAIK you cannot see the output of the validationQuery within a Java application because it happens at the container level before the connection is passed to the client.

Keep it simple and use one of the recommended queries.

Related Solutions

Oracle 11g: Part Copy of Production data to Development Environment

Your initial idea was not bad at all. What you can do is store the unwanted partitions with their local indexes in separate tablespaces. Use rman for the cloning but use the SKIP TABLESPACE option to not clone the tablespaces with the unwanted partitions. (assuming online backup) After the clone, the skipped tablespaces have datafiles with status RECOVER.

see RMAN DUPLICATE DATABASE: Options

In the end you just drop the unwanted partitions. To be able to do that you first have to get rid of things like constraints and indexes that need to be re-created later on. This worked in 10gR2. Make sure that you don't drop the last partition of a table, in that case drop the table.

It is a bit of work but certainly possible. If the difference in Volume is huge or there are lots of copies, it might be worth spending some time for it.

Sub query with not equal

I assume you want rows from table 1 that does not exist in table_2 and vice versa:

SELECT col1, 'table_1' as origin
FROM table_1 x
WHERE NOT EXISTS (
    SELECT 1 FROM table_2 y
    WHERE x.col1 = y.cola
)
UNION
SELECT cola, 'table_2' as origin
FROM table_2 x
WHERE NOT EXISTS (
    SELECT 1 FROM table_1 y
    WHERE y.col1 = x.cola
)

Best Answer

Related Solutions

Oracle 11g: Part Copy of Production data to Development Environment

Sub query with not equal

Related Question