In SQL Developer, I want to execute a query that selects data from a table in a different database something like :

SELECT * 
FROM db2.table;

I tried database link & GRANT SELECT ON db2.table TO current_Db; but I still get "table or view does not exist".. Any help is greatly appreciated

CodePudding user response：

If you're connected as current_Db, what is db2, then?

• you said it is a "different database" - I doubt it, somehow. I presume it is actually a "different user in the same database". If that's so, then you should
  • connect as db2
  • grant select on some_table to current_Db
  • connect as current_Db
  • select * from db2.some_table
• if it really is a different database, then you should

  • connect as current_Db

  • create a database link:

      create database link dbl_db2 
      connect to db2 
      identified by its_password 
      using <connect string to db2 database>
    

  • select * from some_table@dbl_db2

Based on your comment, it looks that it is the 1st option, after all - two users in the same database. Step-by-step:

SQL> show user
USER is "SCOTT"
SQL> create table test as
  2    select 'Littlefoot' name from dual;

Table created.

SQL> grant select on test to mike;

Grant succeeded.

SQL> connect mike/lion
Connected.
SQL> select * from scott.test;

NAME
----------
Littlefoot

SQL>

CodePudding user response：

HR_Db is the first database with the table EMPLOYEES, Snd_Db is the current db where I want to execute the query

[1]: https://i.stack.imgur.com/CBWlz.png

I tried what you suggested but I still get table does not exist..