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

Check index column and contraints for just created table

   <source lang="sql">
   

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.

 </source>
   
  

Decode column position

   <source lang="sql">
  

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>

 </source>
   
  

Query user_indexes and user_ind_columns table

   <source lang="sql">
  

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>

 </source>
   
  

user_indexes join user_ind_columns ic using (table_name,index_name)

   <source lang="sql">
   

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>

 </source>