HELP: Inconsistent results from SQL query run on different servers

Hi,

I am running a simple query (select count(*) from xxx.table_A) on two different servers.
ServerA : Windows 2000 server running Oracle SQL Plus 9.2.0.1.0 ServerB : Windows 2000 server running Oracle SQL Plus 8.0.5.0.0
(My PC client is also running Oracle SQL Plus 9.2.0.1.0)

These two servers connect to an Oracle DB (Oracle 9i (9.2.0.6.0)) located on a different server (Unix) to extract data. These extracts have been running without trouble until we recently had an Oracle Production system upgrade to Oracle 11.

I've checked that the tnsnames files on both servers point to the correct DB server.
The connection string (username/password_at_alias) used to connect to the Oracle database were both the same.

Permissions on the Oracle DB are strict and the username/password has read access to a couple of tables and views only.

The one select statement I am running that is giving me problems joins 1 table and 2 views.
TableA, View1, View2. So I decided to run a simple select on each table to see if I could spot any problems.

When I run a simple select count(*) from TableA or (View1), the results of the statements from the two servers are identical.

When I run the select statment View2, ServerB returns a count of 15,000. while on Server A it returns a count of zero (No records returned).

We have tried everything that we can think of and cannot seem to work out what is happening here. To validate the results I have run the same statements on my PC and get the same results as ServerB (every table/view is populated). A seperate source has run the same query on his PC and returned the same results as Server B both using the same username/password and a DBA username/password.

How can this one server (ServerA) not be returning any count from one view only when 3 other different attempts from different PCs/Servers
(using the same username password) succesfully return the correct
count. Yet ServerA, returns the correct count for the other view and table queries.

If anyone has any suggestions please let me know. Received on Wed Aug 02 2006 - 06:57:20 CDT