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.