DEX VALID
SQL> col column_name for a20
SQL> col column_position for 9999
SQL> r
1* SELECT INDEX_NAME,COLUMN_NAME,COLUMN_POSITION FROM USER_ind_columns where table_name in ('T','EMP','DEPT') ORDER by index_name,column_position
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
DEPT_PK DEPTNO 1
EMP_IDX2 DEPTNO 1
EMP_PK EMPNO 1
EMP_UK EMPNO 1
EMP_UK ENAME 2
T_IDX1 A 1
6 rows selected.
SQL>
SQL>
修改索引状态：
SQL> alter index emp_idx2 invisible;
Index altered.
SQL> select index_name,status,visibility from user_indexes;
INDEX_NAME STATUS VISIBILIT
------------------------------ -------- ---------
EMP_UK VALID VISIBLE
EMP_PK VALID VISIBLE
EMP_IDX2 VALID INVISIBLE
DEPT_PK VALID VISIBLE
T_IDX1 VALID VISIBLE
SQL> alter index emp_idx2 visible;
Index altered.
SQL> select index_name,status,visibility from user_indexes;
INDEX_NAME STATUS VISIBILIT
------------------------------ -------- ---------
EMP_UK VALID VISIBLE
EMP_PK VALID VISIBLE
EMP_IDX2 VALID VISIBLE
DEPT_PK VALID VISIBLE
T_IDX1 VALID VISIBLE
SQL>
SQL> alter index emp_idx2 unusable;
Index altered.
SQL> select index_name,status,visibility from user_indexes;
INDEX_NAME STATUS VISIBILIT
------------------------------ -------- ---------
EMP_UK VALID VISIBLE
EMP_PK VALID VISIBLE
EMP_IDX2 UNUSABLE VISIBLE
DEPT_PK VALID VISIBLE
T_IDX1 VALID VISIBLE
SQL> alter index emp_idx2 rebuild;
Index altered.
SQL> select index_name,status,visibility from user_indexes;
INDEX_NAME STATUS VISIBILIT
------------------------------ -------- ---------
EMP_UK VALID VISIBLE
EMP_PK VALID VISIBLE
EMP_IDX2 VALID VISIBLE
DEPT_PK VALID VISIBLE
T_IDX1 VALID VISIBLE
SQL>
SQL>
SQL>
判断外键索引是否存在：
方法一：
SET VERIFY OFF;
SET LINESIZE 200;
COL IND_COLUMN FOR A25;
COL CONS_COLUMN FOR A25;
COL TAB_NAME FOR A20;
COL OWNER FOR A25;
select distinct a.owner owner,
a.constraint_name cons_name,
a.table_name tab_name,
b.column_name cons_column,
nvl(c.column_name, '***Check index****') ind_column,
c.index_name
from dba_constraints a, dba_cons_columns b, dba_ind_columns c
where constraint_type = 'R'
and a.owner = upper('&&user_name')
and a.owner = b.owner
and a.constraint_name = b.constraint_name
and b.column_name = c.column_name(+)
and b.table_name = c.table_name(+)
and b.position = c.column_position(+)
order by tab_name, ind_column;
#############################
SQL> conn amy/rhys
Connected.
SQL> drop index emp_idx2;
Index dropped.
SQL> conn sys/root as sysdba
Connected.
SQL> SET VERIFY OFF;
SET LINESIZE 200;
SQL> SQL> COL IND_COLUMN FOR A25;
SQL> COL CONS_COLUMN FOR A25;
SQL> COL TAB_NAME FOR A20;
SQL> COL OWNER FOR A25;
SQL> select distinct a.owner owner,
2 a.constraint_name cons_name,
3 a.table_name tab_name,
b.column_name cons_column,
4 5 nvl(c.column_name, '***Check index****') ind_column,
6 c.index_name
7 from dba_constraints a, dba_cons_columns b, dba_ind_columns c
8 where constraint_type = 'R'
9 and a.owner = upper('&&user_name')
10 and a.owner = b.owner
11 and a.constraint_name = b.constraint_name
12 and b.column_name = c.column_name(+)
13 and b.table_name = c.table_name(+)
14 and b.position =