SQLDeveloper: Unable to desc a table that exists

oracleoracle-sql-developer

I have a table, let's call it MYTABLE.

In SQLDeveloper, I can see the data in the table using:

SELECT * FROM MYTABLE

However, when I do DESC MYTABLE, it gives me an error:

ERROR: object MYTABLE does not exist.

If I try the same thing from sqlplus, there is no error and I get a listing of the columns in the table.

I checked dba_all_tables and there is a row for the table.

select * from dba_all_tables where table_name = 'MYTABLE';

returns 1 row as expected.

Any ideas as to what I'm missing?

The command works fine with a different table (MYSCHEMA.MYTABLE2), so it's likely not a sql developer bug.

The same table exists in several schemas:

select owner, object_type, count(*) from dba_objects where object_name='MYTABLE' and owner='MYSCHEMA' group by owner, object_type;

MYSCHEMA    TABLE SUBPARTITION  128 
MYSCHEMA    TABLE PARTITION 16
MYSCHEMA    TABLE   1

Unfortunately, I can't provide a set of steps to reproduce this independently because these tables already exist in the schema I'm working on. My best guess right now is that something went wrong during the table creation long ago, and I can't go back and look at the source code that was executed back then because of a SCCS change in the company.

MYTABLE is not a synonym or mview.

Best Answer

This must be because of MYTABLE being a synonym of a synonym, which is not supported on SQL Developer 4.1.3(must be a bug). Reference

I have three users user3, user2 and user1

SQL> conn user3/user3
Connected.
SQL> create table mytable(id number);

Table created.

SQL> grant select on mytable to user2 with grant option;  

Grant succeeded.

SQL> conn user2/user2
Connected.
SQL> create synonym mytable for user3.mytable;

Synonym created.

SQL> grant select on mytable to user1;

Grant succeeded.

SQL> conn user1/user1
Connected.

SQL>  create synonym mytable for user2.mytable;

Synonym created.

SQL> desc mytable;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> conn / as sysdba       
Connected.
SQL> select table_name from dba_all_tables where table_name='MYTABLE';

TABLE_NAME
------------------------------
MYTABLE

SQL> select owner from dba_tables where table_name='MYTABLE';

OWNER
------------------------------
USER3

Now try to describe it on SQL Developer.

desc mytable;

ERROR: object MYTABLE does not exist
Related Question