Oracle Index - How To Recover a Dropped Index

How To Recover a Dropped Index

Q

How To Recover a Dropped Index? - Oracle DBA FAQ - Managing Oracle Table Indexes

✍: FYIcenter.com

A

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

2007-05-02, 6783👍, 0💬

What Happens to Indexes If You Drop a Table
What Happens to Indexes If You Drop a Table? - Oracle DBA FAQ - Managing Oracle Table Indexes If you drop a table, what happens to its indexes? The answer is that if a table is dropped, all its indexes will be dropped too. Try the following script to see yourself: CREATE TABLE student (id NUMBER(5) ... 2007-05-02, 6234👍, 0💬

What Happens to the Indexes If a Table Is Recovered
What Happens to the Indexes If a Table Is Recovered? - Oracle DBA FAQ - Managing Oracle Table Indexes 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 yo... 2007-05-03, 4547👍, 0💬

How To Rebuild an Index
How To Rebuild an Index? - Oracle DBA FAQ - Managing Oracle Table Indexes 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 T... 2007-04-27, 4461👍, 0💬

Can You Drop an Index Associated with a Unique or Primary Key Constraint
Can You Drop an Index Associated with a Unique or Primary Key Constraint? - Oracle DBA FAQ - Managing Oracle Table Indexes You can not delete the index associated with a unique or primary key constraint. If you try, you will get an error like this: ORA-02429: cannot drop index used for enforcement o... 2007-05-02, 4442👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.