3

I was given this view that when I query it using OracleSQLDeveloper it returns nothing, but if I use Toad it does return records!

I cannot inspect the definition of the view, and no errors are logged. The query is pretty simple:

select * from weirdview ;

So I have questions:

  1. It must be something different in the session/connection parameters, right?
  2. Is there a way to trace on server side what went wrong? I think there must be exceptions raised but ignored, would that be possible?
  3. How can I list the session parameters from the client that does see results so I can duplicate that configuration in the others.

regards

  • 2
    I suspect poorly created VPD rules. Look for login triggers. – Michael Kutz Jul 26 '18 at 23:42
  • Are you using the same database connection information (user ID, ORACLE_SID, host, etc.) on both clients? What version of Oracle? (Add the version tag to your question) – Mark Stewart Jul 27 '18 at 15:55
2

The TNS Listener running on the server hosting the database has a log file located (on Unix-type servers using Oracle recommended naming) at

/u01/app/oracle/diag/tnslsnr/$HOSTNAME/listener/trace/listener.log

The file listener.log may be named something else if you have multiple listeners. That log file will show the connection parameters presented by users connecting to the database via the TNS Listener. I would compare the information in the log for the two connections to see the differences. And if that looks the same, Michael Kutz' comments would be accurate; there may be a login trigger that looks at the program connecting; if you look at the table v$session you can see the program column might be used to restrict access.

  • 1
    thanks, this is really helpful for normalizing future cases and speed up detection of disparities. Regarding this case in particular, it turns out, the other person wasn't connecting with the same user after all (she was connecting with Toad AND another user), despite a couple times she assured so. cheers – Gerardo S Jul 30 '18 at 20:48
  • One thing I learned 30+ years ago: Never believe what the user says! ;) – Mark Stewart Jul 31 '18 at 3:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.