SQLDeveloper: Unable to desc a table that exists

Question

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.

Answer 1

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 

Answer 2

This error may happen when two objects are created with the same name. This problem occurs with at least version Version 17.4.1.054.

For example, if a table and a trigger share the same name:

create table mytable(a number);

create or replace trigger mytable before insert on mytable for each row
begin
    null;
end;
/

Then the command desc MYTABLE; will fail with this error:

ERROR:                               
------------------------------------ 
ERROR: object MYTABLE does not exist 

You can workaround the problem by either changing one of the object names or using double-quotes around the table name:

desc "MYTABLE";

Answer 3

I am experiencing the same problem on a table with subpartitioning. Other subpartitioned tables are fine. I can do a desc successfully in TOAD.

Looks like an edge-case bug in SQLDeveloper.

Answer 4

I think this is because of your table has subpartitions. The error you got is not an oracle error (i.e. not an ORA error). This seems to be tool specific error.