Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Intermittent error in distributed server link, SQL Server 2005 to Oracle

Posted on 2009-05-19
Medium Priority
Last Modified: 2012-05-07

We have an Oracle server linked to an MS SQL 2005 server (running on Windows 2003 Server), and are running distributed queries from the SQL side, e.g.:

      select * from (oracle_linked_servername)..table.column

Periodically the connection seems to "break", and the following will be displayed in SSMS:

     OLE DB provider "OraOLEDB.Oracle" for linked server "IPOSpire" returned message "Table does not exist.".
     Msg 7311, Level 16, State 2, Line 1
     Cannot obtain the schema rowset "DBSCHEMA_INDEXES" for OLE DB provider "OraOLEDB.Oracle" for linked server "IPOSpire". The provider supports the interface, but returns a failure code when it is used.

I've tried to Google the issue, and apparently this error is often due to a security issue - however, I haven't seen anyone else report this as an _intermittent_ error. Why would the connection sometimes work and sometimes fail? Many thanks, and 1,000 points to whoever can solve it!
Question by:CESD123
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 40

Assisted Solution

mrjoltcola earned 1400 total points
ID: 24425474
Can you monitor the Oracle side and verify the sessions prior to, during and after the session "break"

Does this happen when there is a lot of distributed activity?

Did you check the Oracle alert log to see if there are any messages?

Is the schema user for the link under any profile limits?

Accepted Solution

CESD123 earned 0 total points
ID: 24425736
Hello mrjoltcola,

Thanks for your response. I've already asked our Oracle administrator to check the logs on his end, and will also forward your comments to see if that gives him more ideas on what to check for.
LVL 40

Expert Comment

ID: 24425807
What immediately came to mind is a possible resource problem on the Oracle side, such as max processes or cursors being reached in a case where a lot of SQL Server activity is happening. Since you didn't say if this was an idle or busy database, it is worth a short. It should show in the alert log, if so, and will give your DBA clues as to what to set (increase PROCESSES, etc.).

He may also turn on session auditing for the db link user to see what exactly is going on, using fine grained auditing for select statements against the remote table.

Another idea, he can view AWR / ADDM snapshots via Enterprise Manager to see if any instance metrics are spiking during the time of the error.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 48

Assisted Solution

schwertner earned 600 total points
ID: 24425830
Check for firewalls, timeouts, antiviruses that in most cases are installed by default and disturbed the connection.

Curiosly sometimes the Networkcards and switches are out, or recently the network administrators has changed IP adresses of important nodes: DNS, default gateway.

Have you recently changed the names of the servers or the IP?
Have they static IP or are DHCP?

Author Comment

ID: 24426669
Hello, and thanks again.

I'm not able to answer most of these questions, but have passed them on to the appropriate administrators. Regarding this:

     Does this happen when there is a lot of distributed activity?

No, to the best of my knowledge there is very little distributed activity.

Author Comment

ID: 24532742

Thanks to everyone for their comments. We believe this was caused by a version mismatch between the Oracle host on the db server, and the Oracle client on server attempting to connect. After updating the client's version to match, the problem seems to have stopped.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question