An issue came up on the Geoserver users mailing list the other day, which I myself have had in the past. It goes like this:

Lets say you have a table a and table b that you want to publish through Geoserver. a belongs to schema GIS1 and b belongs to schema GIS2. Now, it would be nice to be able to set up one datastore that can connect to Oracle with a username that has access to both tables, and pool connections between them.

Unfortunately it doesn't quite work like that.

The reason being is that you must specify a schema in the datastore configuration for the Oracle plugin to know how to find the spatial metadata of a table, unless the table belongs to the user that you are connecting with. For example, you could try connecting as user GIS1. If GIS1 has access to GIS2.b, you will be able to see it in your list of available layers in the geoserver layer chooser. If you select it and try to use it, you will most likely get an exception from the Oracle driver, along the lines of:

ORA-00942: table or view does not exist

Why? Well there are two different ways a user can access spatial metadata for tables in Oracle.

1. USER_SDO_GEOM_METADATA - This is a special view that gives a user access to the spatial metadata for all of the spatially enabled tables that they own. It identifies geometry objects that the user owns using columns table_name and column_name. Geoserver will first try to use this view to obtain spatial metadata for your selected table. In our case, GIS1 does not own table b, so it won't come up in the list. So it tries the other technique.

2. ALL_SDO_GEOM_METADATA - This view is available to all users, and shows the spatial metadata for all spatial tables in the Oracle instance. That means that b will be in there, but ALL_SDO_GEOM_METADATA contains one vital extra column that it uses to tell tables apart; the schema_name. Because this view shows tables from all schemas, we need to be able to identify them!

Now, geoserver won't remember that b comes from a schema other than GIS1, which we are trying to access it with. So it will find the appropriate metadata in ALL_SDO_GEOM_METADATA, but it will only check for the table using table_name and column_name, which will not uniquely identify it, if there is another table called b in another schema!

That won't be a problem if b is the only spatially enabled table in the Oracle instance that is called b. What will be a problem is when Oracle tries to use the table. If you have no schema specified in your geoserver datastore, geoserver (well, geotools, actually) will test access to b like this:

SELECT MY_PK FROM B WHERE 0=1

That will result in the oracle error ORA-00942, above.

So how do we get around all this? I really want to access multiple schemas in geoserver but I only want to connect with one username so that I can pool connections across schemas!

JNDI.

Using JNDI you can set up a datasource in your application container (i.e. Tomcat) that can be referenced by multiple Geoserver datasources. You still need to hard-code a username and password into the connection config for Tomcat, but you can set up a Geoserver datasource for each schema that you want to access, which uses that connection pool, but that allows you to specify a schema name!

And voila, we have got multiple datasources in Geoserver that point to different schemas, using the same username and password for connecting, but we are actually pooling connections across datasources. And best of all Geoserver knows exactly where to find all your tables.

If you need help setting up connection pooling for Geoserver, they have a great guide in amongst the rest of their super documentation.