Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Managing Oracle Table Indexes

By: FYIcenter.com

Part:   1  2  3   4 

(Continued from previous part...)

How To Recover a Dropped Index?

If you have the recycle bin feature turned on, dropped indexes are stored in the recycle bin. But it seems to be command to restore a dropped index out of the recycle bin. FLASHBACK INDEX is not a valid statement. See the following script:

ALTER SESSION SET recyclebin = on;
Statement processed.

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  birth_date DATE NOT NULL,
  social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.

CREATE INDEX student_birth_date ON student(birth_date);
Index created.

DROP INDEX STUDENT_BIRTH_DATE;
Index dropped.

SELECT object_name, original_name, type, droptime
  FROM recyclebin;
OBJECT_NAME             ORIGINAL_NAME      TYPE  DROPTIME
----------------------- ------------------ ----- ----------
BIN$1LlsjTxERKq+C7A==$0 STUDENT_BIRTH_DATE INDEX 2006-04-01

FLASHBACK INDEX student_birth_date TO BEFORE DROP;
ORA-00905: missing keyword

What Happens to the Indexes If a Table Is Recovered?

If you dropped a table, and recovered it back from the recycle bin, what happens to its indexes? Are all indexes recovered back automatically? The answer is that all indexes will be recovered, if you recover a dropped table from the recycle bin. However, the indexes' names will not be the original names. Indexes will be recovered with the system assigned names when they were dropped into the cycle bin. The following SQL script shows you this behavior:

ALTER SESSION SET recyclebin = on;
Statement processed.

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  birth_date DATE NOT NULL,
  social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.

CREATE INDEX student_birth_date ON student(birth_date);
Index created.

SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME              TABLE_NAME            UNIQUENES
----------------------- --------------------- ---------
SYS_C004141             STUDENT               UNIQUE
SYS_C004142             STUDENT               UNIQUE
STUDENT_BIRTH_DATE      STUDENT               NONUNIQUE 

DROP TABLE student;
Table dropped.

SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'STUDENT';
no data found

FLASHBACK TABLE student TO BEFORE DROP;
Flashback complete.

SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME                     TABLE_NAME UNIQUENES
------------------------------ ---------- ---------
BIN$K47Sg+udQv2tDUW5cWAIrQ==$0 STUDENT    UNIQUE 
BIN$6WI0gc79QNqLSNGp2H2Q1Q==$0 STUDENT    UNIQUE 
BIN$9HwZermkRt+9gonHS/klsQ==$0 STUDENT    NONUNIQUE 

If you have trouble running the FLASHBACK statement on your Web interface, run it with SQL*Plus.

How To Rebuild an Index?

If you want to rebuild an index, you can use the "ALTER INDEX ... REBUILD statement as shown in the following SQL script:

SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'EMPLOYEES';
INDEX_NAME              TABLE_NAME            UNIQUENES
----------------------- --------------------- ---------
EMP_EMAIL_UK            EMPLOYEES             UNIQUE
EMP_EMP_ID_PK           EMPLOYEES             UNIQUE
EMP_DEPARTMENT_IX       EMPLOYEES             NONUNIQUE
EMP_JOB_IX              EMPLOYEES             NONUNIQUE
EMP_MANAGER_IX          EMPLOYEES             NONUNIQUE
EMP_NAME_IX             EMPLOYEES             NONUNIQUE

ALTER INDEX EMP_NAME_IX REBUILD;
Statement processed.

(Continued on next part...)

Part:   1  2  3   4