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

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?

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.

StackExchange Database Administrators Q#12376, answer score: 11