Oracle – How to Deal with Table in ALL_OBJECTS but Not Selectable

oracletable

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.

Best Answer

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";

Related Solutions

“ORA-00942: table or view does not exist” when deleting from a very real table in the user’s schema

Once I had a similar Problem. To shorten a rather long story, just flush your shared pool.

ALTER SYSTEM FLUSH SHARED_POOL;

Somehow Oracle seems to create an corrupted entry in the statement cache. Problem has been resolved after flush. After that, we were unable to reproduce it.

You need some administrative privileges to flush it.

One final note: ORA-00942 does not exist in the database documentation at all.

According to my notes, we used this Oracle version.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set. Both your original procedure and your new procedure could then use the function. The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY. In this way the core query code would not be duplicated. Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/


create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/


SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;

Related Question