ORA says NO DATA FOUND

-


Linked servers always have a surprise error hidden under its selvee, Managing these issue magnets is a huge task for a DBA , recently i was stumbled upon a same suprise invloving a oracle linked server.

The linked server "oracle_db" is present on my prod db server essentially for select and insert purposes, Developers tested the linked server with couple of select querries and all was fine until the deployed a procedure on server and executing the same gave a alien error

OLE DB provider "OraOLEDB.Oracle" for linked server "oracle_db" returned message "ORA-01403: no data found"

inspecting the code it was seen that the table from linkedserver was directly involved in a " inner join" and other joins ,re running the same satement produced the given error . The code snippet was pinned, diagnosis and resolution was underway

After a thorogh R&D it was found that either taking the whole oracle side output into a #Temp table and then using that #temp table in join would solve it and other was using join hints.

Join Hints helps sql server to enforce a join between two tables. As being a remote query involved in the troublesome part of code the Join Hint 'Remote' fit the discription well.

Remote Join Hint specifies the join operation is performed on the server of the right table, useful when the left table is a local table and the right table is a remote table.

  • If the right table is local, the join is performed locally.
  • If both tables are remote but from different data sources, REMOTE causes the join to be performed on the site of the right table.
  • If both tables are remote tables from the same data source, REMOTE is not required.

REMOTE can be used only for INNER JOIN operations. we used the REMOTE clause in the query where the oracle table was called and it worked smoothly.


Comments

Post a Comment

Popular posts from this blog

SQL Server’s STATISTICS IO

-
Image
Usually the goal of performance tuning is to make your query run faster.  One of the easiest ways to get a faster query is to reduce the volume of data a query is processing.  STATISTICS IO makes it easy to see how much data SQL Server is actually processing. How it is going to help?  The data it shows acts as a measuring for your performance tuning.  It provides a good way of knowing the number of io Sql server has gone to fetch the data  It is session specific only How to Use? -- For current session only SET STATISTICS IO ON; GO How to read the output?  Logical reads: The number of pages SQL Server had to read from the memory in order to process and return the results of your query.  Query performs slower if number of pages read is high.   Physical reads:  this read happens when the page requested is not present in the cache and needs to be fetched from files on the disk, this operation causes a IO wait.   Worktables/Workfiles:  sql server cre...
Read more

(session ID -2 is not valid) Dealing with Negative Session ids in Sql Server

-
Image
  SQL server being a complex mechanism of handling processes throughout their life cycle often gives out interesting anomalies, One such was encountered today on one of my production servers, there was a huge blocking on chain of processes and at the root of this chain was a session with the weirdest session id  The session id literally was " -2 "   ,  it is hard to make sense out of it as SQL server always works with positive spids. Research on this is itself a tedious job. After the R&D phase it was somewhat easier to diagnose the problem. The root cause refers to a phase of distributed transaction, A distributed transaction is referred when a process is extended over more than one servers i.e Process A of server 1 needs data to be manipulated on Server 2, so SQL server uses MSDTC (linked server) service to carry this process from one server to another. Going back to our issue, now According to msdn and other online resources, the session id -2 means that ...
Read more