Collections:
Dropped Tables with Indexes in Oracle
What Happens to Indexes If You Drop a Table in Oracle?
✍: FYIcenter.com
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.
⇒ Recover a Dropped Index in Oracle
⇐ Error: Cannot Drop Index on Primary Key in Oracle
2019-04-22, 1285👍, 0💬
Popular Posts:
How To Calculate DATETIME Value Differences Using the DATEDIFF() Function in SQL Server Transact-SQL...
What are binary literals supported in SQL Server Transact-SQL? Binary literals in Transact-SQL are s...
How To Query Multiple Tables Jointly in MySQL? If you want to query information stored in multiple t...
How To Drop an Index in Oracle? If you don't need an existing index any more, you should delete it w...
What Is an Oracle Tablespace in Oracle? An Oracle tablespace is a big unit of logical storage in an ...