oracle-databaseoracle11ginstantclient

Oracle ORA-01805 on Oracle 11g database


Our Oracle 10g database was recently upgraded to 11g. The database is running on a Windows Server 2003 X64 machine. In SQL queries from a .NET application that access a table that has a TIMESTAMP (6) WITH TIME ZONE data columns, I am getting the following exception.

System.Data.OracleClient.OracleException : ORA-01805: possible error in date/time operation

The suggested action for the exception is to ensure that the client and server are the same version:

ORA-01805: possible error in date/time operation Cause: The timezone files on client and server do not match. Operation can potentially result in incorrect results based on local timezone file. Action: Please ensure client and server timezone versions are same.

I've ran the following queries to check the timezone on the database in question. I haven't found information on how I set the timezone (or change the timezone file) for the client.

SELECT dbtimezone FROM DUAL;
select * from v$timezone_file;

DBTIMEZONE 
---------- 
+00:00     

FILENAME             VERSION                
-------------------- ---------------------- 
timezlrg_14.dat      14     

I assume the client is referring to the Instant Client I have installed, which is version 11_2? I'm running the queries through a System.Data.OracleClient.OracleConnection as provided by the .NET Framework. U I assume by "timezone version" it's referring to the timezone file versions. I don't see where the instant client has a timezone file. Any suggestions are appreicated.


Solution

  • Use "genezi -v" to know the timezone file version.

    Here is a sample in my Linux box:

    $ genezi -v
    Client Shared Library 32-bit - 11.2.0.2.0
    
    System name:    Linux
    Release:    2.6.32-34-generic
    Version:    #77-Ubuntu SMP Tue Sep 13 19:39:17 UTC 2011
    Machine:    x86_64
    
    Operating in Instant Client mode.
    Small timezone file = timezone_14.dat
    Large timezone file = timezlrg_14.dat