How do I find an object that doesn't seem to exist in the objects table?

Question

I have an object called cot_ntn_pi_v. I was told this was a synonym. It doesn't appear in the all_synonyms table. It looks like a view or table but I can't find it in the all objects table. I can select from it, but I can't drop it as it 'doesn't exists' and I can't create a new table with the same name as 'the name is already used by another object.'

Am I going mad or doing something really stupid?

Answer 1

Object types in the same namespace as a table are:

• Stand-alone procedures
• Stand-alone stored functions
• Packages
• User-defined types
• Sequences
• Views
• Private Synonyms
• Materialized Views

Therefore it is probably one of those types. If you can select from it then it rules out the first five leaving it to be either a table, view, private synonym or materialized view.

When you searched in all_objects etc. you did use upper case? For example,

select *
  from ALL_OBJECTS
 where OBJECT_NAME = 'COT_NTN_PI_V'; 

If you use some tool like SQL Developer or Toad you can let it describe the object for you. Highlight the name in the tool and hit Shift-F4 in Developer or F4 in Toad. Toad provides a lot of description on the object while Developer, in the Details tab, will have a row with TABLE_NAME or MVIEW_NAME in it and that'll show you what it is.

Once you know what it is then it will make it easier to know how to drop it.

Answer 2

You may not be able to see the object in the ALL_% tables if you don't have any grants on the object itself, so check in the DBA_OBJECTS table (you will need grants/an appropriately granted user to do this):

select * 
from DBA_OBJECTS
where object_name = 'COT_NTN_PI_V';

If that doesn't produce any output you can check in the raw Oracle data dictionary tables with this query:

select u.name as owner, o.name as object_name, 
 decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
 11, 'PACKAGE BODY', 12, 'TRIGGER',
 13, 'TYPE', 14, 'TYPE BODY',
 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
 32, 'INDEXTYPE', 33, 'OPERATOR',
 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
 39, 'LOB PARTITION', 40, 'LOB SUBPARTITION',
 43, 'DIMENSION',
 44, 'CONTEXT', 47, 'RESOURCE PLAN',
 48, 'CONSUMER GROUP',
 51, 'SUBSCRIPTION', 52, 'LOCATION', 56, 'JAVA DATA', 'UNKNOWN') as type,
o.ctime, o.mtime,
 to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
 decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID') as status
 from sys.obj$ o, sys.user$ u
 where o.owner# = u.user#
 and o.linkname is null
 and (o.type# not in (1 , 10) or
 (o.type# = 1 and 1 = (select 1
 from sys.ind$ i
 where i.obj# = o.obj#
 and i.type# in (1, 2, 3, 4, 6, 7, 9))))
 and o.name = 'COT_NTN_PI_V';