CODEWITHSUNDEEP
oracle-databasejdbcoracle11gdatabase-indexes
Joachim Sauer
Joachim Sauer

Reputation: 308159

How do I get all indices on a Table in Oracle via JDBC, even if they are owned by different users?

Yes, I know about DatabaseMetadata.getIndexInfo, but it doesn't seem to do what I want.

I've got two users/schemas, let's call them A and B.

There's a table in A called TAB. The user B created an index on A.TAB, let's call that index IND.

The information that I want is: what indices are there on the table TAB in the schema A (a.k.a with the owner A). I don't care about the owner of the indices, just that they are on that specific table.

Experimenting with getIndexInfo I found out the following things:

Having traced the SQL the JDBC driver executes on getIndexInfo(null, "A", "TAB", false, true), I got this:

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

As you can see both table_name and i.owner are restricted to being TAB. This means that this query will only return index information that is owned by the same user as the table.

I can think of three possible workarounds:

  1. always create the index and the table in the same schema (i.e. let them have the same owner). Unfortunately that's not always an option.
  2. Query with schema set to null. This would get ugly as soon as two schemata contained the same table name (because there's no way to find out on which table (i.e. which table owner) a given schema is).
  3. execute that SQL directly (using executeQuery()). I'd rather not fall down to this level, unless it's absolutely unavoidable.

None of those workarounds look particularly pleasing to me, but if nothing else works, I might have to fall back to direct SQL execution.

Both the Database and the JDBC Driver are at 11.2.0.2.0.

So basically my questions are:

  1. Is this a bug in the JDBC driver, or is there some logic behind it that I'm unaware of?
  2. Is there a simple and reasonably portable way to get Oracle to give me the information that I need?

Upvotes: 2

Views: 2452

Answers (2)

user330315
user330315

Reputation:

always create the index and the table in the same schema (i.e. let them have the same owner). Unfortunately that's not always an option.

That would be my preferred way of doing it.

Query with schema set to null. This would get ugly as soon as two schemata contained the same table name (because there's no way to find out on which table (i.e. which table owner) a given schema is)

Of course you can find that out, because the result set returned by getIndexInfo() does contain the correct schema for each table. But you can't find out in which schema the index is.

execute that SQL directly

I would actually use a modified version of that query that also returns the schema for each index to alleviate the identification of the index.

But again: I would also create the index and the table in the same schema.

Upvotes: 1

Miguel Veloso
Miguel Veloso

Reputation: 1095

I suggest you query directly the Oracle dictionary tables, BUT start with:

select * from dba_indexes

Using that view it should be almost trivial to get the info you need.

However, accesing the dba_ tables and views requires the user to have special privileges, but since you don't want to give DBA privilege to everyone, you can just:

grant select any dictionary to username

connected as system or sys so the selected user can query the dictionary.

Just in case you want to explore Oracle's dictionary, try:

select * from dict

Best regards.

Upvotes: 1

Related Questions