Oracle PL/SQL/System Tables Views/user ind columns

Check index column and contraints for just created table

    
SQL>
SQL> create table movie(
  2           movie_no               integer              constraint movie_pk primary key using index (create index movie_pk_idx on movie(movie_no) )
  3          ,title                  varchar2(40)
  4          ,rating                 char(4)
  5          ,type                   varchar2(20)
  6          ,star                   varchar2(40)
  7          ,qty                    number
  8          ,price                  number
  9  );
Table created.
SQL>
SQL> select index_name, table_name, column_name from user_ind_columns where table_name = "MOVIE";
MOVIE_PK_IDX                   MOVIE
MOVIE_NO

1 row selected.
SQL>
SQL> select constraint_name, table_name, column_name from user_cons_columns where table_name = "MOVIE";
MOVIE_PK                       MOVIE
MOVIE_NO

1 row selected.
SQL>
SQL> drop table movie cascade constraints;
Table dropped.

Decode column position

   
SQL>
SQL>
SQL> column table_name format a15 word_wrapped
SQL> column index_name format a25 word_wrapped
SQL> column columns format a40
SQL>
SQL> break on table_name skip 1
SQL>
SQL> select table_name,
  2       index_name,
  3  rtrim(
  4       max( decode( column_position, 1, column_name, "" ) )||","||
  5       max( decode( column_position, 2, column_name, "" ) )||","||
  6       max( decode( column_position, 3, column_name, "" ) )||","||
  7       max( decode( column_position, 4, column_name, "" ) )||","||
  8       max( decode( column_position, 5, column_name, "" ) )||","||
  9       max( decode( column_position, 6, column_name, "" ) )||","||
 10       max( decode( column_position, 7, column_name, "" ) )||","||
 11       max( decode( column_position, 8, column_name, "" ) )||","||
 12       max( decode( column_position, 9, column_name, "" ) )||","||
 13       max( decode( column_position, 10, column_name, "" ) )||","||
 14       max( decode( column_position, 11, column_name, "" ) )||","||
 15       max( decode( column_position, 12, column_name, "" ) )||","||
 16       max( decode( column_position, 13, column_name, "" ) )||","||
 17       max( decode( column_position, 14, column_name, "" ) )||","||
 18       max( decode( column_position, 15, column_name, "" ) )||","||
 19       max( decode( column_position, 16, column_name, "" ) ), "," )
 20       columns
 21  from user_ind_columns
 22  where rownum < 50
 23  group by table_name,index_name
 24  /
TABLE_NAME      INDEX_NAME                COLUMNS
--------------- ------------------------- ----------------------------------------
EMP_CHANGES     SYS_C005784               MSGID
UPPER_ENAME     SYS_IOT_TOP_15816         X$ENAME,X$RID
DEPT_AND_EMP    SYS_C005826               DEPTNO
                SYS_C005827               EMPS
AQ$_EMP_CHANGES SYS_IOT_TOP_16251         MSGID,SUBSCRIBER#,NAME,ADDRESS#
_G
TABLE_NAME      INDEX_NAME                COLUMNS
--------------- ------------------------- ----------------------------------------

AQ$_EMP_CHANGES SYS_IOT_TOP_16249         MSGID,SUBSCRIBER#,NAME,ADDRESS#
_H

AQ$_EMP_CHANGES SYS_IOT_TOP_16254         SUBSCRIBER#,NAME,QUEUE#,MSG_PRIORITY,MSG
_I                                        _ENQ_TIME,MSG_STEP_NO,MSG_CHAIN_NO,MSG_L
                                          OCAL_ORDER_NO,MSGID

TABLE_NAME      INDEX_NAME                COLUMNS
--------------- ------------------------- ----------------------------------------
AQ$_EMP_CHANGES SYS_C005787               SUBSCRIBER_ID
_S

AQ$_EMP_CHANGES SYS_IOT_TOP_16247         NEXT_DATE,TXN_ID,MSGID
_T

COMPILE_SCHEMA_ COMPILE_SCHEMA_TMP_PK     OBJECT_NAME,OBJECT_TYPE
TMP

10 rows selected.
SQL>

Query user_indexes and user_ind_columns table

   
SQL>
SQL>
SQL> SELECT dic.table_name, dic.index_name,
  2         DECODE(di.uniqueness, "UNIQUE", "YES", "NO")uniqueness,
  3         dic.column_position, dic.column_name
  4  FROM   user_indexes di, user_ind_columns dic
  5  WHERE  dic.table_name  = di.table_name
  6  AND    dic.index_name  = di.index_name
  7  and    rownum < 50
  8  ORDER BY dic.table_name, dic.index_name,
  9           di.uniqueness desc, dic.column_position;
no rows selected
SQL>

user_indexes join user_ind_columns ic using (table_name,index_name)

    
SQL>
SQL>
SQL>
SQL> col table_name  format a15
SQL> col index_name  format a15
SQL> col unq         format a9
SQL> col column_name format a15
SQL>
SQL> set linesize 80
SQL>
SQL> break on table_name skip 1 -
>       on index_name        -
>       on unq               -
>       on status
SQL>
SQL> select   table_name
  2  ,        index_name
  3  ,        i.uniqueness as unq
  4  ,        i.status
  5  ,        ic.column_name
  6  from     user_indexes     i  join
  7           user_ind_columns ic using (table_name,index_name)
  8  where    table_name in ("EMP","DEPARTMENTS")
  9  order by table_name
 10  ,        index_name
 11  ,        unq
 12  ,        ic.column_position;
no rows selected
SQL>
SQL> --clear breaks
SQL>