amazon-web-servicesoracle-databaseamazon-rdsaws-dmsora-00942

AWS RDS Oracle Error while Querying - ORA-00942: table or view does not exist

AWS RDS Oracle Error while Querying -

ORA-00942: table or view does not exist

I have done test data migration from MySQL to Oracle on AWS RDS DBs. Post Migration, I am running a few Select count(*) queries for validation. I am getting the error ORA-00942: table or view does not exist. I am using SQL Developer as a Client tool from an AWS EC2 instance. I can visualize sample data from the GUI of SQL Developer. But while running the query I am getting the error.

Few relevant queries:

1.
SHOW USER; --ADMIN

2.
SELECT owner FROM all_objects
WHERE object_type IN ('TABLE','VIEW')
AND object_name = 'ureentity'; --ADMIN

3.
Select count(*) from ADMIN.ureentity; --ORA-00942: table or view does not exist

4.
SELECT * FROM USER_TAB_PRIVS WHERE GRANTEE = 'ADMIN';

5.
GRANT SELECT ON ureentity TO ADMIN;

Can someone help with it?

Solution 
  • GRANT SELECT ON ureentity TO ADMIN;

    You should not need to grant access to a table to its owner. Access is implicit and irrevocable.

    SELECT owner FROM all_objects
WHERE object_type IN ('TABLE','VIEW')
AND object_name = 'ureentity';

    If you get output from this query, with the object_name forced to be lower-case, then it implies that the object was created with quotes around the name, and in lower-case: e.g. create table "ureentity" ...

    Because the object was created using quotes and forcing case-sensitivity into the name, it must always be referenced using quotes in the future, otherwise Oracle will default to a case-insensitive reading of the name and will not find the object.

    Try this, while connected as ADMIN:

    Select count(*) from "ureentity";

    Note that in general, creating case-sensitive names in Oracle is considered bad practice. Object names should not be quoted in DDL statements - then ureentity and UREENTITY could be used without quotes and would be considered equivalent.

    These are equivalent and case-insensitive:

    Select count(*) from ureentity;
Select count(*) from UREENTITY;
Select count(*) from UreEntity;

    These are not equivalent to the first three statements, or to each other:

    Select count(*) from "ureentity";
Select count(*) from "UREENTITY";
Select count(*) from "UreEntity";