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

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

Up vote 2 down vote favorite 1 share g+ share fb share tw.

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: the first argument catalog seems to be entirely ignored by the Oracle JDBC driver. The second argument schema restricts which table statistics is returned and the owner of the index unique and approximate do (roughly) what they should (except that giving approximate=false will actually execute an update statistics statement). 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: 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. 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). 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: Is this a bug in the JDBC driver, or is there some logic behind it that I'm unaware of? Is there a simple and reasonably portable way to get Oracle to give me the information that I need? Oracle jdbc oracle11g database-indexes link|improve this question edited Sep 1 '11 at 8:05 asked Aug 23 '11 at 9:04Joachim Sauer72.4k5116218 71% accept rate.

The simple and portable method to get Oracle to give you what you want is SQL. The thrid party tool would appear to have limitations that you cannot work around. Use the SQL you identified from tracing and alter the 2nd "and i.

Owner = 'A'" line to be " = 'B'" – Karl Aug 23 '11 at 9:45 @Karl: yeah, it will probably boil down to that, but I dislike it, because I handle pretty much all other databases just fine with getIndexInfo. And hard-coding that SQL could break whenever Oracle feels like it (I don't actually know how stable the necessary structures are, because luckily I hadn't had to go down to this level before). – Joachim Sauer Aug 23 '11 at 9:53 @Karl: by the way: it's not a third-party tool.

It's the JDBC driver that Oracle itself provides for that database. – Joachim Sauer Aug 23 '11 at 9:54 Looks like a bug. I would recommend reporting this issue to Oracle support.

– Tahir Akhtar Aug 24 '11 at 9:34 1 @Joachim Sauer. The good news is that the Oracle data dictionary is fairly stable. Not totally stable but small incremental changes which tend to be well documented.

And yes, Oracle, like all vendors, make assumptions about how things are going to be used. Thankfully the basic interface is reliable and usable. – Karl Aug 24 '11 at 20:25.

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.

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.

Regarding using schema set to null: Unfortunately on the index information part it returns i. Owner as table_schema, so that value will be set to the schema of the index, rather than the table. – Joachim Sauer Sep 1 '11 at 7:01 @Joachim Sauer: then I'd go for option 1) or 3) – a_horse_with_no_name Sep 1 '11 at 8:03.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.