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.

3
  • Unrelated, but: you should not use analyze table any longer. That has been replaced by the package dbms_stats ages ago.
    – user1822
    Jul 10, 2019 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. Jul 10, 2019 at 7:40
  • As I said: that's unrelated to your current problem, so it doesn't really matter
    – user1822
    Jul 10, 2019 at 7:44

1 Answer 1

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 and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.