Oracle Management Index (VII) Oracle Display index information

1. Display current user index information (dba_indexes,all_indexes,user_indexes)

03:41:37 sql> Select Index_name,index_type,uniqueness

03:41:49 2 from User_indexes;

Index_name Index_type Uniquenes

--------------- --------------- ---------

Pk_emp NORMAL UNIQUE

Pk_dept NORMAL UNIQUE

R_empno_ind Normal/rev nonunique

Empno_indx NORMAL nonunique

Indx_ename function-based nonunique

NORMAL

Pk_sale Iot-top UNIQUE

Pk_deptno NORMAL nonunique

7 rows selected.

2. Display index Columns

03:44:27 sql> Col column_name for A15

03:44:43 sql> R

1 Select Column_name,column_position,column_length

2* from User_ind_columns

column_name column_position Column_length

--------------- --------------- -------------

DEPTNO 1 22

ID 1 22

EMPNO 1 22

ENAME 1 10

sys_nc00008$ 1 10

DEPTNO 1 22

EMPNO 1 22

ENAME 1 10

8 rows selected.

3, show the location and size of the index segment

03:46:13 sql> Select Tablespace_name,segment_type,bytes

03:46:22 2 from User_segments;

Tablespace_name Segment_type BYTES

--------------- --------------- ----------

USERS INDEX 65536

USERS INDEX 65536

USERS INDEX 65536

USERS TABLE 65536

USERS TABLE 65536

USERS INDEX 65536

USERS TABLE 65536

USERS CLUSTER 65536

USERS TABLE 65536

USERS TABLE 65536

USERS TABLE 65536

USERS INDEX 65536

USERS TABLE 65536

USERS INDEX 65536

USERS TABLE 65536

USERS01 INDEX 131072

USERS01 TABLE 131072

Tablespace_name Segment_type BYTES

--------------- --------------- ----------

USERS02 TABLE 65536

Rows selected.

4. Display Function Index

03:47:39 sql> Select column_expression from User_ind_expressions;

Column_expression

--------------------------------------------------------------------------------

LOWER ("ename")

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/