sqlplusでスキーマの持つインデックスの一覧を見たいんです
USER_INDEXESとUSER_IND_COLUMNSから引っ張ってくる。
SQL> conn hr/hr@xe SQL> col table_name format a16 trunc SQL> col index_name format a24 SQL> col uniqueness format a4 trunc SQL> col column_name format a24 SQL> select i.table_name, i.index_name, i.uniqueness, c.column_name 2 from user_indexes i, user_ind_columns c 3 where i.index_name = c.index_name 4 order by index_name,table_name,column_position 5 ; TABLE_NAME INDEX_NAME UNIQ COLUMN_NAME ---------------- ------------------------ ---- ------------------------ COUNTRIES COUNTRY_C_ID_PK UNIQ COUNTRY_ID DEPARTMENTS DEPT_ID_PK UNIQ DEPARTMENT_ID DEPARTMENTS DEPT_LOCATION_IX NONU LOCATION_ID EMPLOYEES EMP_DEPARTMENT_IX NONU DEPARTMENT_ID EMPLOYEES EMP_EMAIL_UK UNIQ EMAIL EMPLOYEES EMP_EMP_ID_PK UNIQ EMPLOYEE_ID EMPLOYEES EMP_JOB_IX NONU JOB_ID EMPLOYEES EMP_MANAGER_IX NONU MANAGER_ID EMPLOYEES EMP_NAME_IX NONU LAST_NAME EMPLOYEES EMP_NAME_IX NONU FIRST_NAME JOB_HISTORY JHIST_DEPARTMENT_IX NONU DEPARTMENT_ID TABLE_NAME INDEX_NAME UNIQ COLUMN_NAME ---------------- ------------------------ ---- ------------------------ JOB_HISTORY JHIST_EMPLOYEE_IX NONU EMPLOYEE_ID JOB_HISTORY JHIST_EMP_ID_ST_DATE_PK UNIQ EMPLOYEE_ID JOB_HISTORY JHIST_EMP_ID_ST_DATE_PK UNIQ START_DATE JOB_HISTORY JHIST_JOB_IX NONU JOB_ID JOBS JOB_ID_PK UNIQ JOB_ID LOCATIONS LOC_CITY_IX NONU CITY LOCATIONS LOC_COUNTRY_IX NONU COUNTRY_ID LOCATIONS LOC_ID_PK UNIQ LOCATION_ID LOCATIONS LOC_STATE_PROVINCE_IX NONU STATE_PROVINCE REGIONS REG_ID_PK UNIQ REGION_ID 21行が選択されました。
