ORA-00942 cannot find table

Question

I ran these statements:

create table SAMPLE(
     ID INT
     );
     
INSERT INTO SAMPLE VALUES(1);
COMMIT;

GRANT INSERT, SELECT, UPDATE, DELETE ON SAMPLE TO NEWLEC;
COMMIT;

But Eclipse says cannot find the table.

How can I resolve it?

Answer 1

You are connecting as the newlec user, and unqualified references to tables will look for them in that schema, not under other users.

You can qualify the table name:

SELECT * FROM real_owner.SAMPLE

For example, if you were connected as HR when you created the table, you would do:

SELECT * FROM HR.SAMPLE

---

You could also create a public synonym, which is probably overkill and can cause other issues and confusion; or create a private synonym for the newlec user which points to the rea table, e.g.:

CREATE SYNONYM SAMPLE FOR real_owner.SAMPLE;
SELECT * FROM SAMPLE;

so the unqualified query now sees the synonym, and through that the real table owned by someone else. Or you could set your current_schema after logon:

ALTER SESSION SET CURRENT_SCHEMA=real_owner;
SELECT * FROM SAMPLE;

so all unqualified queries now see objects owned by that user, not newlec.

However, if this is a basic assignment on a beginner course then you are probably just expected to qualify the name.