CODEWITHSUNDEEP
sqloracle-database
Nitesh
Nitesh

Reputation: 31

Unable to access record even if table has records

I have 2 schemas on my DB i.e. Host and Staging. I have given SELECT grant on table host.ctrl_test to user staging. But when I fire select query on host.ctrl_test table from staging user, it returns 0 rows.

SQL> conn host

Enter password:

Connected.

SQL> select count(1) from host.ctrl_test;

  COUNT(1)
----------
     14455

SQL> conn staging

Enter password:

Connected.

SQL> select count(1) from host.ctrl_test;

  COUNT(1)
----------
         0

Upvotes: 3

Views: 122

Answers (1)

Del
Del

Reputation: 1589

Possible reasons for this behavior include:

  1. Forgot to commit or using wrong database 99.999% of these problems are caused by forgetting to commit, or unintentionally looking at the wrong database. To avoid the inevitable "did you commit?" question, it helps to post fully-reproducible test cases in a simple text-based environment like SQL*Plus, with a prompt that shows the user and database name.
  2. Temporary table For global temporary tables or private temporary tables the data will only be visible to one session.
  3. Virtual Private Database/Translation/Advanced Rewrite Oracle has a few features where you can silently replace a query with another query, depending on the user. Normally you can see these weird changes going on in an execution plan, in the Note section.
  4. View that depends on session setting The table is really a view, and contains conditions that depend on session settings.
  5. Wrong-results bug/Corruption About one in a trillion times there may be a corrupt index that is giving wrong results, but only for certain sessions that happen to use the index.

Upvotes: 1

Related Questions