如何通过 JDBC 获取 Oracle 表中的所有索引，即使它们属于不同用户？

Question

是的，我知道关于DatabaseMetadata.getIndexInfo,但它似乎没有达到我想要的效果。

我有两个用户/模式，我们称它们为 A 和 B。

A 中有一个名为 TAB 的表。用户B在A.TAB上创建了一个索引，我们将该索引称为IND。

我想要的信息是：模式 A 中的表 TAB 上有哪些索引（又名所有者 A< /代码>）。我不关心索引的所有者，只关心它们位于该特定表上。

通过使用 getIndexInfo 进行实验，我发现了以下内容：

• 第一个参数 catalog 似乎完全被 Oracle JDBC 驱动程序忽略。
• 第二个参数schema限制返回哪个表统计信息和索引的所有者
• 唯一 和 approximate （大致）做它们应该做的事情（除了给出 approximate=false 将实际执行更新统计信息语句）。

跟踪 JDBC 驱动程序在 getIndexInfo(null, "A", "TAB", false, true) 上执行的 SQL 后，我得到了以下结果：

select null as table_cat,
       owner as table_schem,
       table_name,
       0 as NON_UNIQUE,
       null as index_qualifier,
       null as index_name, 0 as type,
       0 as ordinal_position, null as column_name,
       null as asc_or_desc,
       num_rows as cardinality,
       blocks as pages,
       null as filter_condition
from all_tables
where table_name = 'TAB'
  and owner = 'A'
union
select null as table_cat,
       i.owner as table_schem,
       i.table_name,
       decode (i.uniqueness, 'UNIQUE', 0, 1),
       null as index_qualifier,
       i.index_name,
       1 as type,
       c.column_position as ordinal_position,
       c.column_name,
       null as asc_or_desc,
       i.distinct_keys as cardinality,
       i.leaf_blocks as pages,
       null as filter_condition
from all_indexes i, all_ind_columns c
where i.table_name = 'TAB'
  and i.owner = 'A'
  and i.index_name = c.index_name
  and i.table_owner = c.table_owner
  and i.table_name = c.table_name
  and i.owner = c.index_owner
order by non_unique, type, index_name, ordinal_position

正如您所看到的两者 < code>table_name 和 i.owner 仅限于TAB。这意味着此查询将仅返回与表属于同一用户的索引信息。

我可以想到三种可能的解决方法：

1. 始终在同一模式中创建索引和表（即让它们具有相同的所有者）。不幸的是，这并不总是一个选择。
2. 将 schema 设置为 null 的查询。一旦两个模式包含相同的表名，这就会变得很难看（因为无法找出给定模式是在哪个表（即哪个表所有者）上）。
3. 直接执行该 SQL（使用executeQuery()）。我宁愿不跌到这个地步，除非这是绝对不可避免的。

这些解决方法看起来都不是特别令我满意，但如果没有其他办法，我可能不得不回退到直接 SQL 执行。

数据库和 JDBC 驱动程序均为 11.2.0.2.0。

所以基本上我的问题是：

1. 这是 JDBC 驱动程序中的错误，还是背后有一些我不知道的逻辑？
2. 是否有一种简单且合理可移植的方法可以让 Oracle 为我提供所需的信息？

Answer 1

我建议您直接查询 Oracle 字典表，但首先：

select * from dba_indexes

使用该视图，获取您需要的信息几乎是微不足道的。

然而，访问 dba_ 表和视图需要用户具有特殊权限，但由于您不想向每个人授予 DBA 权限，因此您可以：

grant select any dictionary to username

作为 system 或 sys 连接，以便所选用户可以查询字典。

万一您想探索 Oracle 词典，请尝试：

select * from dict

此致。

Answer 2

始终在同一架构中创建索引和表（即让它们具有相同的所有者）。不幸的是，这并不总是一个选择。

这将是我首选的做法。

架构设置为 null 的查询。一旦两个模式包含相同的表名，这就会变得很难看（因为无法找出给定模式在哪个表（即哪个表所有者）上）

当然你可以找到它，因为返回的结果集getIndexInfo() 确实包含每个表的正确架构。但您无法找出索引属于哪个模式。

直接执行该SQL

我实际上会使用该查询的修改版本，该版本还返回每个索引的架构以减轻索引的识别。

但同样：我还会在同一架构中创建索引和表。