Oracle SQL Developer - 无法看到表

Oracle SQL Developer - tables cannot be seen

提问by sammy

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.

我使用的是 SQL Developer 1.1。我最近升级到 SQL Developer 3。升级后，我注意到我的一些连接不显示它们的表。我可以对表格进行选择并显示数据。我可以看到其他对象，如视图等。我仍然可以使用我的旧版本，并且可以看到针对相同模式列出的所有表。不过，还有其他连接显示正常。

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

似乎无法找到这方面的信息。我尝试移除过滤器，但没有用。

Did anybody run into this? Please help.

有人遇到过这个吗？请帮忙。

回答by Michael Munsey

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.

您要查找的表可能处于不同的架构中。有几个选项。您可以单击连接下树中的其他用户，或右键单击连接并选择模式浏览器，然后选择所需的模式。

回答by Thayz

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

我已经尝试了 Michael Munsey 建议的两个选项并且对我有用。

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/

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

回答by Vinay s.c.

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

从 中选择“其他用户”并选择您的用户（架构），在该用户（架构）下您将能够看到您的表和视图。

回答by Mikhail K

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

我在 sqlDeveloper64-3.0.4.34 和 sqlDeveloper64-3.1.07.42 中有同样的问题。

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

根据https://forums.oracle.com/forums/thread.jspa?threadID=2202388看来，JDBC 驱动程序中存在一个与“带外中断”有关的错误 - 基本上是一个低级别的 TCP 问题。

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

解决方法是使用 JVM 属性启动 sql developer -Doracle.net.disableOob=true我为 3.0 和 3.1 尝试了这个解决方案并且它有效。

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

我相信我已经确定了导致某些用户而不是其他用户出现这些问题的原因。JDBC 驱动程序中似乎存在一个与“带外中断”有关的错误 - 基本上是一个低级别的 TCP 问题。该错误似乎以多种方式表现出来。到目前为止，我已经确定使用共享连接（特别是使用 Vista 或 Windows 7）和通过 VPN（任何操作系统）连接作为常见场景。在所有情况下，没有 DBA 访问权限也是一个问题。

首先，让我解释一下为什么 DBA 访问会有所作为。当我们第一次访问任何特定的数据字典视图时，我们首先尝试查看是否可以访问视图的 DBA 版本（或者在某些情况下是 tab$ 等）。这些视图比普通的 USER 版本高效得多，所以我们想尽可能地使用它们。我们每个会话只检查每个 DBA 视图一次（并且只在需要时），但我们最终可能会检查对一堆视图的访问。

当我们进行这项检查时，OOB 错误似乎又抬头了。我们应该从数据库得到一个很好的、简单的响应。但是，在发生错误的情况下，此低级别网络错误反而会导致发生错误，从而使连接进入不可用状态。这将导致所有连接关闭错误。似乎有一种解决方法 - JDBC 驱动程序支持禁用 OOB。但是，这样做会影响取消执行语句的能力，因此我一般不建议使用变通方法，但它应该可以解决用户遇到此特定问题的情况。

要启用变通方法，需要设置 Java 系统属性- oracle.net.disableOob=true。您可以通过两种方式进行设置。第一种是在命令行上将其作为 sqldeveloper -J-Doracle.net.disableOob=true 传递。当然，这仅在您通常从命令行运行时才有效。您还可以向 sqldeveloper.conf 文件（位于 +sqldeveloper\bin+ 下）添加一行。该行将是AddVMOption -Doracle.net.disableOob=true

我们正在研究其他解决方案，但目前解决方法应该使您能够使用 SQL Developer。

- 约翰

SQL 开发团队

回答by Codo

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:

您可能无权访问 SQL Developer 3 正在查询的元表之一。如果我选择了正确的查询，它会运行以下 SELECT 语句：

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 SCHEMAand INCLUDE_SYNS. Set SCHEMAto your username and INCLUDE_SYNSto 0.

尝试运行此语句以获取完整的错误消息，指示哪个表不存在（相当于“由于缺少访问权限而不可见”）。SQL Developer将询问值SCHEMA和INCLUDE_SYNS。设置SCHEMA为您的用户名和INCLUDE_SYNS0。

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

SQL Developer 1.1 可能使用了一个与您的访问权限相关的更简单的查询。

回答by vapcguy

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.)

在 Oracle 中，它只会向您显示属于您登录帐户所拥有的架构（MS SQL Server 中的数据库）的表。如果该帐户不拥有/没有创建任何内容，即使您对数据库中的所有内容都拥有权限，您也将看不到任何内容！（这与 MS SQL Server Management Studio 相反，在那里您可以看到您拥有的任何权利，并且所有者始终是“dbo”，禁止某些管理员进入并出于某些不可预见的原因更改它。）

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.

表示“admin”是拥有它的用户，因此您进入“Other Users > Admin”并展开“Tables”，它就在那里。

回答by Bill Blazek

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.

我在我的 Mac 上遇到了这个问题。通过卸载它并删除 /Users/aa77686/.sqldeveloper 文件夹来修复它。卸载而不删除该文件夹并没有修复它。
然后重新下载并重新安装。
启动它，添加连接，它工作正常。
退出它，重新启动它几次，到目前为止它每次都正确显示表格等。

回答by James

3.1 didn't matter for me.

3.1 对我来说无关紧要。

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

我花了一段时间，但我设法找到了 2.1 版本并在这里尝试：http: //www.oracle.com/technetwork/testcontent/index21-ea1-095147.html

1.2 http://www.oracle.com/technetwork/testcontent/index-archive12-101280.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.

但这也不起作用，仍然没有表格，所以它看起来像是经过许可的东西。

回答by HyNeck

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

将 sys.external_tab$ 上的选择授予 [myUser]；对我来说有效。谢谢科多

回答by sammy

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

SQL Developer 3.1 修复了这个问题。不过，它目前是早期采用者版本。