• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

lookupIndexColumnns

I have an index on a table with a name IDX_UNIQUE_T1 and column name is SYS_NC00072$

I am trying to figure out the columns for this inde from user_ind_columns but can't tell what it is.

any ideas how>
0
sam15
Asked:
sam15
1 Solution
 
johanntagleCommented:
Should be simple.  What does the ff query give you:

select column_name from user_ind_columns where index_name='IDX_UNIQUE_T1'
0
 
devindCommented:
Try following to get DDL for Index

select  DBMS_METADATA.GET_DDL('INDEX','PERSON_IDX') from DUAL;

for more examples refer to http://blogs.sun.com/mandalika/entry/oracle_extracing_the_table_index
0
 
sam15Author Commented:
the first statement gives me the same column name "SYS_NC00072$" for column name.

the second SQL did not work. I think 9202 has issues or a bug with this the DBMS_METADATA or it is not supported.

I found the DDL using TOAD and the index is function based index.

CASE WHEN BKNO > 20000 THEN MEDIUM||','||TO_CHAR(BKNO) ELSE NULL END

not sure why the FBI does not show a valid column name. I think thsi index is enforcing a uniqe values on the combination of the two columns for numbers larger than 20000.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now