Oracle索引-新建 维护 监督使用等

1、查询用户的索引

SQL> select index_name,table_name,tablespace_name,index_type,uniqueness,status from dba_indexes
    where owner='SCOTT';
2、新建索引
SQL> create index scott.emp_ename_idex on scott.emp(job)
  2  pctfree 20
  3   storage(initial 100K next 100K pctincrease 0 maxextents 100)
  4   tablespace lianxi_index;
Index created

SQL> select index_name,table_name,tablespace_name,index_type,uniqueness,status from dba_indexes
  2   where owner='SCOTT';
INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                INDEX_TYPE                  UNIQUENESS STATUS
------------------------------ ------------------------------ ------------------------------ --------------------------- ---------- --------
PK_DEPT                        DEPT                           USERS                          NORMAL                      UNIQUE     VALID
PK_EMP                         EMP                            USERS                          NORMAL                      UNIQUE     VALID
EMP_ENAME_IDEX                 EMP                            LIANXI_INDEX                   NORMAL                      NONUNIQUE  VALID

3、修改索引
SQL> alter index scott.EMP_ENAME_IDEX   rebuild pctfree 25 storage(next 250K);
Index altered
4、修改索引所在的区段

SQL> alter index scott.EMP_ENAME_IDEX allocate extent;
Index altered

5、联机创建索引  
alter index scott.scott.EMP_ENAME_IDEX  rebuild online;

6、回收木有用的空间
alterindex scott.scott.EMP_ENAME_IDEX deallocate unused;

7、合并碎片
alterindex scott.scott.EMP_ENAME_IDEX coalesce;

================================索引使用情况进行监督========================================================================
1、开启监督语句

SQL> alter index scott.EMP_ENAME_IDEX monitoring usage;
Index altered

2、查看使用情况
SQL> select * from v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
3、关闭监督语句
SQL> alter index scott.EMP_ENAME_IDEX nomonitoring usage;
Index altered