查询用户的索引

select index_name,table_name,tablespace_name,

index_type,uniqueness , status

from dba_indexes where owner='SCOTT';

查询用户的索引列

select index_name,table_name,column_name,

index_owner,table_owner

from dba_ind_columns

where table_owner='SCOTT';

查看索引的各种初始化因子

select index_name,table_name,tablespace_name,

pct_free,pct_increase,initial_extent,

next_extent, status

from dba_indexes where owner='SCOTT';

重建和维护索引

alter index scott.emp_ename_idx rebuild

pctfree 40

storage (next 300k);

查看索引segment

select segment_name,segment_type,tablespace_name,extents

from dba_segments

where owner='SCOTT'

and segment_type='INDEX';

给索引添加相应的extent

alter index scott.emp_ename_idx allocate extent;

回收索引端

alter index scott.emp_ename_idx deallocate unused;

合并索引碎片

alter index scott.emp_ename_idx coalesce;

联机重建索引：

alter index scott.emp_ename_idx rebuild online;

标识索引的使用情况

1.启用索引监控

alter index emp_ename_idx monitoring usage;

2.执行相关查询

select ename,job ,sal from scott.emp

where ename like 'C%';

3.查看索引是否使用

select * from v$object_usage;

4.禁用索引监控

alter index emp_ename_idx nomonitoring usage;