Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Any help would be great, Thanks!

share|improve this question
    
If you don't have permissions on the item it may not show in the ALL_* tables. You may need an administrative login and look in the DBA_* data dictionary tables. –  ConcernedOfTunbridgeWells Feb 7 '12 at 11:50

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
Yes using sql developer and shift + f4 I was able to find out what I needed! Thanks :) –  BON Feb 8 '12 at 9:32

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';
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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