Hi Andrea,
Thanks for the info. I'd forgotten about that SQL (our DBA's apparently did
too!).
I've got a "improved" version in my notes - which has some extra
configuratibility:
SELECT LAST_ACTIVE_TIME, last_load_time, module, parsing_schema_name,
> elapsed_time total_elapsed_time_micros, executions number_executions,
> DECODE(executions,0,0,round((elapsed_time/executions)/1000000,3))
> avg_elapsed_time_s, sql_id, sql_fulltext
> FROM v$sql
> WHERE 1=1
> and last_active_time > CURRENT_TIMESTAMP - interval '24' hour /*all within
> last day*/
> -- and parsing_schema_name IN ('OSMM') /* Search within a tablespace */
> -- and last_active_time > to_date('24/01/2011 16:13:00', 'DD/MM/YYYY
> HH24:MI:SS') /*Search since a given date*/
> -- and UPPER(sql_text) like UPPER('SELECT NULL AS table_cat,%')
> /*Search for specific SQL*/
> -- and sql_id = 'c749bc43qqfz3' /*A given SQL_ID*/
> ORDER BY LAST_ACTIVE_TIME DESC;
----
That said, I'm no closer to finding the cause. I've manually run probably a
hundred prospective queries from there into SQL Developer and all of them
run fine. Even made sure they were running as the same user.
The closest I can find that it could possibly be is:
SELECT META.SDO_LAYER_GTYPE
> FROM ALL_INDEXES INFO
> INNER JOIN MDSYS.USER_SDO_INDEX_METADATA META
> ON INFO.INDEX_NAME = META.SDO_INDEX_NAME
> WHERE INFO.TABLE_NAME = 'ROAD_ACCNTS_2010_WSHIRE'
> AND REPLACE(meta.sdo_column_name, '"') = 'XY_COORD'
> AND INFO.TABLE_OWNER = 'WCCDATA'
But again it runs just fine - the result is "no rows selected".
or:
SELECT * FROM MDSYS.USER_SDO_INDEX_METADATA WHERE ROWNUM < 2
Same result.
----
Some google found:
http://osgeo-org.1560.x6.nabble.com/Layer-publishing-from-Oracle-OCI-not-working-with-Geoserver-2-1-3-td4663532.html
Which in turn points to:
http://osgeo-org.1560.x6.nabble.com/getting-data-from-OracleDB-in-production-td3801495.html#a3801496
So it's not unique to me and it's a long standing thing, although for me
it's not impeding any functions as best I can tell.
I can't say I'm surprised Oracle is remaining so opaque. Can we be certain
that this is what Oracle is returning and that it's not the JDBC thing
misconstruing the "no results" result?
Anyone have any other thoughts on how to demystify this?
Thanks,
Jonathan
On 23 April 2014 13:10, Andrea Aime <[email protected]> wrote:
> On Wed, Apr 23, 2014 at 12:55 PM, Jonathan Moules <
> [email protected]> wrote:
>
>> Hi List,
>> I'm getting the following in my logs.
>>
>> 2014-04-22 14:48:03,547 WARN [geotools.jdbc] - Failure occurred while
>>> looking up the primary key with finder:
>>> org.geotools.jdbc.HeuristicPrimaryKeyFinder@e944077
>>> java.sql.SQLException: ORA-01031: insufficient privileges
>>> at
>>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
>>> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
>>> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
>>> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
>>> at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
>>> at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946)
>>> at
>>> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
>>> at
>>> oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1614)
>>> at
>>> oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1579)
>>> at
>>> oracle.jdbc.OracleDatabaseMetaData.getIndexInfo(OracleDatabaseMetaData.java:3485)
>>> at
>>> org.apache.commons.dbcp.DelegatingDatabaseMetaData.getIndexInfo(DelegatingDatabaseMetaData.java:327)
>>> at
>>> org.apache.commons.dbcp.DelegatingDatabaseMetaData.getIndexInfo(DelegatingDatabaseMetaData.java:327)
>>> at
>>> org.geotools.jdbc.HeuristicPrimaryKeyFinder.getPrimaryKey(HeuristicPrimaryKeyFinder.java:69)
>>> at
>>> org.geotools.jdbc.CompositePrimaryKeyFinder.getPrimaryKey(CompositePrimaryKeyFinder.java:52)
>>
>>
>>
>> I'd like to give my Oracle user the right permissions, but from the error
>> we (including the DBA's) can't figure out what GeoServer is doing and
>> therefore what permissions are required. Does anyone know what priviledges
>> we need to give to the database to get this to disappear?
>>
>
> I don't, but the search for primary keys is done using the JDBC driver
> official methods for the purpose:
>
> ResultSet primaryKey = metaData.getPrimaryKeys(null, databaseSchema,
> tableName);
>
> and if that fails, falling back on searching for unique indexes:
>
> ResultSet uniqueIndex = metaData.getIndexInfo(null, databaseSchema,
> tableName,
> true, true);
>
> ( more info here:
> http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html)
>
> You might want to inquiry Oracle directly about it (which queries are run,
> and so on).
> If you look back in the archives, there is also meantioning a view that
> contains the full
> list of queries run against a Oracle instance, v$sql
>
> An example query that I used in the past is:
>
> SELECT last_active_time, LAST_LOAD_TIME, module, parsing_schema_name,
> elapsed_time total_elapsed_time_micros, executions number_executions,
> DECODE(executions,0,0,round((elapsed_time/executions)/1000000,3))
> avg_elapsed_time_s, sql_id, sql_fulltext
> FROM v$sql WHERE last_active_time > to_date('04/12/2013 14:58:45',
> 'DD/MM/YYYY HH24:MI:SS')
> and parsing_schema_name IN ('GEOSERVER')
> ORDER BY LAST_ACTIVE_TIME DESC
>
> Cheers
> Andrea
>
> --
> ==
> Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
> for more information.
> ==
>
> Ing. Andrea Aime
> @geowolf
> Technical Lead
>
> GeoSolutions S.A.S.
> Via Poggio alle Viti 1187
> 55054 Massarosa (LU)
> Italy
> phone: +39 0584 962313
> fax: +39 0584 1660272
> mob: +39 339 8844549
>
> http://www.geo-solutions.it
> http://twitter.com/geosolutions_it
>
> -------------------------------------------------------
>
--
This transmission is intended for the named addressee(s) only and may
contain sensitive or protectively marked material up to RESTRICTED and
should be handled accordingly. Unless you are the named addressee (or
authorised to receive it for the addressee) you may not copy or use it, or
disclose it to anyone else. If you have received this transmission in error
please notify the sender immediately. All email traffic sent to or from us,
including without limitation all GCSX traffic, may be subject to recording
and/or monitoring in accordance with relevant legislation.
------------------------------------------------------------------------------
Start Your Social Network Today - Download eXo Platform
Build your Enterprise Intranet with eXo Platform Software
Java Based Open Source Intranet - Social, Extensible, Cloud Ready
Get Started Now And Turn Your Intranet Into A Collaboration Platform
http://p.sf.net/sfu/ExoPlatform
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
