1

I am seeing a weird behaviour in my Oracle DB schema around a 'missing' table.

The end goal is to gather statistics for all the tables that our team uses. All those tables have a common prefix - rca.

So, I am fetching the list of tables using:

SELECT * 
FROM ALL_OBJECTS 
where OBJECT_NAME like 'rca%' 
  AND OBJECT_TYPE = 'TABLE';

This query returns a table called rca_related_materials.

We're using a function developed by some other team that does the gather stats job for us. We just pass the owner_name and the table_name to that function.

Calling that function for this table name or running

analyze table rca_related_materials compute statistics for table;

or even,

select * 
from rca_related_materials;

returns the below Error report

SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

How can I get this entry removed from the ALL_OBJECTS table?

PS: I see the same entry coming up in USER_TABLES and DBA_TABLES also.

  • Unrelated, but: you should not use analyze table any longer. That has been replaced by the package dbms_stats ages ago. – a_horse_with_no_name Jul 10 '19 at 7:37
  • @a_horse_with_no_name: We're actually using an internal function that does the job for us. We just pass the owner_name and the table_name to that function. Will update the question with this info. – Sarath Chandra Jul 10 '19 at 7:40
  • As I said: that's unrelated to your current problem, so it doesn't really matter – a_horse_with_no_name Jul 10 '19 at 7:44
6

Database objects with lowercase names need to have their name enclosed in double quotes when referenced

Also, you left out the schema name.

Try

select * 
from SCHEMA_NAME."rca_related_materials";

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.