（转）oracle索引语句整理

1.创建索引  

create [unique]index index_name on table_name(col_1,col_2,...)  

tablespace tabspace_name;  

2.改变索引的存储参数  

alter index index_name pctfree 30 storage(initial 200k next 200k);  

3.给索引手工分配一个分区  

alter index index_name allocate extent (size 200k datafile '$ORACLE/oradata/..' );  

4.释放索引中没用的空间  

alter index index_name deallocate unused;  

5.索引重建  

alter index index_name rebuild tablespace tablespace_name;  

6.普通索引和反转索引的互换  

alter index index_name rebuild tablespace tablespace_name reverse;  

 

7.重建索引时，不锁表  

alter index index_name rebuild online;  

8.给索引整理碎片  

alter index index_name COALESCE;  

9.分析索引,事实上是更新统计的过程  

analyze index index_name validate structure;  

desc index_state;  

drop index index_name;  

analyze table t compute statistics for ta
ble for all indexes for all indexed column
s;

10.监视索引是否被用到  

alter index index_name monitoring usage;  

11.取消监视  

alter index index_name nomonitoring usage;  

12.有关索引信息的视图  

select * from   

dba_indexes/dba_ind_columns/user_indexes/user_ind_columns/dbs_ind_
eXPressions/v$object_usage;