What Happens to Indexes If You Drop a Table in Oracle?

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) 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

No records in USER_INDEXES view found for your name STUDENT, after you dropped STUDENT table.

 

⇒Managing Oracle Table Indexes

⇒⇒Oracle Database Tutorials