oracle索引sys_nc,Oracle教程之管理索引（七）--Oracle显示索引信息

1、显示当前用户索引信息(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、显示索引列

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、显示索引段的位置和尺寸

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

18 rows selected.

4、显示函数索引

03:47:39 SQL> select column_expression from user_ind_expressions;

COLUMN_EXPRESSION

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

LOWER("ENAME")