[sql] Oracle SQL Developer - tables cannot be seen

I was using SQL Developer 1.1. I have recently upgraded to SQL Developer 3. After the upgrade, I noticed that some of my connections do not show their tables. I can do a select on the tables and the data is shown. I can see other objects like views etc. I can still use my older version and can see all the tables listed fine for the same schemas. There are other connections that show up fine though.

Cannot seem to find information on this. I tried removing the filter but it didn't work.

Did anybody run into this? Please help.

Launch SQL Developer as administrator

3.1 didn't matter for me.

It took me a while, but I managed to find the 2.1 release to try that out here: http://www.oracle.com/technetwork/testcontent/index21-ea1-095147.html

1.2 http://www.oracle.com/technetwork/testcontent/index-archive12-101280.html

That doesn't work either though, still no tables so it looks like something with permission.

The answer about going under "Other Users" was close, but not nearly explicit enough, so I felt the need to add this answer, below.

In Oracle, it will only show you tables that belong to schemas (databases in MS SQL Server) that are owned by the account you are logged in with. If the account owns/has created nothing, you will see nothing, even if you have rights/permissions to everything in the database! (This is contrary to MS SQL Server Management Studio, where you can see anything you have rights on and the owner is always "dbo", barring some admin going in and changing it for some unforeseeable reason.)

The owner will be the only one who will see those tables under "Tables" in the tree. If you do not see them because you are not their owner, you will have to go under "Other Users" and expand each user until you find out who created/owns that schema, if you do not know it, already. It will not matter if your account has permissions to the tables or not, you still have to go under "Other Users" and find that user that owns it to see it, under "Tables"!

One thing that can help you: when you write queries, you actually specify in the nomenclature who that owner is, ex.

Select * from admin.mytable

indicates that "admin" is the user that owns it, so you go under "Other Users > Admin" and expand "Tables" and there it is.

SQL Developer 3.1 fixes this issue. Its an early adopter release at the moment though.

The tables you are looking for are probably in a different schema. There are a couple of options. You can either click on Other Users in the tree under your connection, or right click on the connection and select Schema Browser and then select the desired schema.

I have the same problem in sqlDeveloper64-3.0.4.34 and sqlDeveloper64-3.1.07.42.

According to https://forums.oracle.com/forums/thread.jspa?threadID=2202388 it appears there is a bug in the JDBC driver having to do with 'Out Of Band Breaks' - basically a low level TCP issue.

The workaround is launch sql developer with JVM property -Doracle.net.disableOob=true I tried this solutions for 3.0 and 3.1 and it works.

So I just quote here the solution from forum:

I believe I have identified what is causing these issues for some users and not others. It appears there is a bug in the JDBC driver having to do with 'Out Of Band Breaks' - basically a low level TCP issue. The bug seems to manifest itself in a number of ways. So far I've identified using shared connections (particularly with Vista or Windows 7) and connecting over VPN (any OS) as common scenarios. In all cases, not having DBA access is also an issue.

First, let me explain why DBA access makes a difference. When we first access any particular data dictionary view, we first try to see if we can get access to the DBA version of the view (or is some cases tab$, etc). These views are much more efficient than the ordinary USER versions, so we want to use them if we can. We only check each DBA view once per session (and only when needed), but we can end up checking for access to a bunch of views.

The OOB bug seems to rear its head when we do this check. We should get a nice, simple response back from the database. However, in the scenarios where the bug is occurring, this low level network bug is instead causing an error to occur that puts the connection into an unusable state. This then results in all the Connection Closed errors. There does appear to be a workaround - the JDBC driver supports disabling OOB. However, doing so will affect the ability to cancel an executing statement, So I wouldn't recommend using the workaround in general, but it should solve the issue for the situations where users are running into this specific problem.

To enable the workaround, a Java system property needs to be set - oracle.net.disableOob=true. You can set this in two ways. The first is to pass it in on the command line as sqldeveloper -J-Doracle.net.disableOob=true. Of course, that only works if you are normally running from the command line. You can also add a line to the sqldeveloper.conf file (located under +sqldeveloper\bin+). There the line would be AddVMOption -Doracle.net.disableOob=true

We are looking into additional resolutions, but for now the workaround should enable you to work with SQL Developer.

- John

SQL Developer Team

You probably don't have access to one of the meta tables that SQL Developer 3 is querying. If I picked the right query, it runs the following SELECT statement:

select * from (
  SELECT o.OBJECT_NAME, o.OBJECT_ID ,'' short_name, decode(bitand(t.property, 32), 32, 'YES', 'NO') partitioned,
                decode(bitand(t.property, 64), 64, 'IOT',
               decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',
               decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null))) iot_type, 
         o.OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME, O.GENERATED, O.TEMPORARY, case when xt.obj# is null then 'N' else 'Y' end EXTERNAL
    FROM SYS.Dba_OBJECTS O ,sys.tab$ t, sys.external_tab$ xt
    WHERE O.OWNER = :SCHEMA
    and   o.object_id = t.obj#(+)
    and   o.object_id = xt.obj#(+)
    AND O.OBJECT_TYPE = 'TABLE' 
union all
SELECT OBJECT_NAME, OBJECT_ID , syn.SYNONYM_NAME short_NAME, decode(bitand(t.property, 32), 32, 'YES', 'NO') partitioned,
                decode(bitand(t.property, 64), 64, 'IOT',
               decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',
               decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null))) iot_type, 
       SYN.TABLE_OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME, O.GENERATED, O.TEMPORARY, case when xt.obj# is null then 'N' else 'Y' end EXTERNAL
              FROM SYS.Dba_OBJECTS O, sys.user_synonyms syn,sys.tab$ t, sys.external_tab$ xt
              WHERE  syn.table_owner = o.owner
              and    syn.TABLE_NAME = o.object_NAME
              and    o.object_id = t.obj#
              and   o.object_id = xt.obj#(+)
              and    o.object_type = 'TABLE'
              and    :INCLUDE_SYNS = 1
)
 where /**/object_name not in (select object_name from recyclebin)
                         AND not object_name like 'BIN$%'

Try to run this statement to get a full error messages indicating which table doesn't exists (which is equivalent to "is not visible due to missing access rights"). SQL Developer will ask for values for SCHEMA and INCLUDE_SYNS. Set SCHEMA to your username and INCLUDE_SYNS to 0.

SQL Developer 1.1 probably used a simpler query that worked with your access rights.

I have tried both the options suggested by Michael Munsey and works for me.

I wanted to provide another option to view the filtered tables. Mouse Right Click your table trees node and Select "Apply Filter" and check "Include Synonyms" check box and click Okay. That's it, you should be able to view the tables right there. It works for me.

Courtesy: http://www.thatjeffsmith.com/archive/2013/03/why-cant-i-see-my-tables-in-oracle-sql-developer/

For me, this wound up being a permissions issue.

I solved the problem by creating a superuser role (CREATE ROLE root WITH SUPERUSER LOGIN PASSWORD 'XXXXX';) and then using that superuser account to connect to the database.

This obviously won't be a viable solution in all situations.

The identity used to create the connection defines what tables you can see in Oracle. Did you provide different credentials when setting up the connection for the new version?

grant select on sys.external_tab$ to [myUser]; worked for me. thanx Codo

Select 'Other Users' from the and select your user(schema), under which you will be able to see your tables and views.

I had this problem on my Mac. Fixed it by uninstalling it AND removing the /Users/aa77686/.sqldeveloper folder. Uninstalling without deleting that folder did not fix it.
Then redownloaded and reinstalled.
Started it up, added connections and it worked fine.
Quit it, restarted it several times and it shows the tables, etc. correctly each time so far.

You need select privileges on All_users view