1、修改索引段存储参数
03:31:28 SQL> alter index scott.indx_ename deallocate unused;
Index altered.
03:31:36 SQL> alter index scott.indx_ename allocate extent(size 1m);
Index altered.
2、重建索引
03:32:48 SQL> alter index indx_ename rebuild;
Index altered.
3、联机重建索引
使用rebuild选项重建索引时，如果其他用户正在表上执行DML操作，那么重建索引将会失败，并显示错误信息“ORA-00054:资源正忙，但指 定一NOWAIT方式获取资源”。为了最小化DML操作的影响，重建索引时，可以使用REBUILD ONLINE选项。示例如下：
ALTER INDEX department_dname REBUILD ONLINE;
4、合并索引
03:37:40 SQL> alter index indx_ename coalesce;
Index altered.
5、删除索引
03:37:40 SQL> drop   index    indx_ename;
6、验证索引
ANALYZE INDEX orders_region_id_idx VALIDATE STRUCTURE;
7、索引监控
SQL> conn scott/tiger
Connected.
SQL> alter index emp_ename_funind monitoring usage;
Index altered.
SQL> select index_name,table_name,monitoring,used from v$object_usage;
INDEX_NAME                     TABLE_NAME      MONITORIN USED
------------------------------ --------------- --------- ---------
EMP_ENAME_FUNIND               EMP             YES       NO
10:01:29 SQL> select * from emp where LOWER(ename)='scott';
EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
SQL> select * from emp where lower(ename)='scott';
EMPNO ENAME           JOB                    MGR HIREDATE                   SAL       COMM     DEPTNO
---------- --------------- --------------- ---------- ------------------- ---------- ---------- ----------
7788 SCOTT           ANALYST               7566 1987-04-19 00:00:00       3000                    20
SQL> select index_name,table_name,monitoring,used from v$object_usage;
INDEX_NAME                     TABLE_NAME      MONITORIN USED
------------------------------ --------------- --------- ---------
EMP_ENAME_FUNIND               EMP             YES       YES