oracledba.help
Schema

Indexes

Create

B-Tree (Default)

CREATE INDEX [schema.]<NewIndexName>
ON [schema.]TableName (col1, col2 ...)
TABLESPACE TableSpaceName;

CREATE INDEX hr.emp_hiredate_ndx ON hr.emp (hire_date) TABLESPACE hr;

Bitmap

CREATE BITMAP INDEX [schema.]<NewIndexName>
ON [schema.]Tablename (col1, col2 ...)
TABLESPACE TableSpaceName;

CREATE BITMAP INDEX hr.emp_dept_ndx ON hr.emp (dept) TABLESPACE hr;

Display

COL index_name FORMAT a35 HEADING 'Index_Name'
COL index_type FORMAT a25 HEADING 'Index_Type'

-- Indexes for a Table

SELECT owner,table_name,index_name,tablespace_name
FROM dba_indexes
WHERE table_name = 'EMP'; 

-- Invalid Indexes

SELECT index_name, index_type, status
FROM dba_indexes
WHERE  status != 'VALID'
AND    owner   = 'SCOTT'; 

-- Parallel Value > 1 [default]

COL owner      FORMAT a20
COL index_name FORMAT a30
COL degree     FORMAT a6

SELECT owner,index_name,degree 
FROM dba_indexes 
WHERE to_number(degree) > 1
AND   owner NOT IN('APEX_040200','AUDSYS','CTXSYS','SYS','SYSTEM','WMSYS','XDB') 
ORDER BY owner,index_name;

To Reset Parallel Back to 1
ALTER INDEX SCOTT.MY_IDX PARALLEL 1;

-- Check If Index Needs to Be Rebuilt
This is derived from Oracle's: How to Determine When an Index Should be Rebuilt? (Doc ID 1373415.1).

PROMPT -- BLEVEL Check
SELECT owner, index_name, table_name, blevel 
FROM dba_indexes 
WHERE BLEVEL > 3;

PROMPT -- Ratio Check
SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*100 as ratio 
FROM INDEX_STATS;

-- Tablespace Index is In

SELECT owner,table_name,index_name,tablespace_name
FROM dba_indexes
WHERE tablespace_name != 'USERS'
AND   owner            = 'SCOTT';

Drop

DROP INDEX [schema.]<NewIndexName>;

DROP INDEX hr.emp_dept_ndx;

Move/Rebuild

Indexes are moved by rebuilding in them in the desired tablespace.

Standard Index

  1. Determine index to move. SELECT index_name FROM dba_indexes WHERE table_name='&TABLE_NAME';
  2. Move/Rebuild
  ALTER INDEX [schema.]<NewIndexName> 
  REBUILD PARALLEL ONLINE TABLESPACE <TablespaceName>;
ALTER INDEX scott.pk_emp REBUILD PARALLEL ONLINE TABLESPACE hr;

Index Partition

  1. Determine index to move.
    SELECT index_name FROM dba_indexes WHERE table_name='&TABLE_NAME';
  2. Determine index partition names.
    SELECT index_name,partition_name,tablespace_name 
    FROM dba_ind_partitions WHERE index_name = '&INDEX_NAME';
  3. Move
    ALTER INDEX index_name REBUILD PARTITION TABLESPACE NOLOGGING;

<- Schema