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:
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
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.
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';
not able to find the object in dba_objects as well as with above raw data query. but when I am creating the objects it says SW_SYSTEM_EVENTS