Home >> FAQs/Tutorials >> Oracle Tutorials
Oracle Tutorials - Recovered Tables with Indexes
By: FYIcenter.com
(Continued from previous topic...)
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.
(Continued on next topic...)
- What Is an Index?
- How To Run SQL Statements through the Web Interface?
- How To Create a Table Index?
- How To List All Indexes in Your Schema?
- What Is an Index Associated with a Constraint?
- How To Rename an Index?
- How To Drop an Index?
- Can You Drop an Index Associated with a Unique or Primary Key Constraint?
- What Happens to Indexes If You Drop a Table?
- How To Recover a Dropped Index?
- What Happens to the Indexes If a Table Is Recovered?
- How To Rebuild an Index?
- How To See the Table Columns Used in an Index?
- How To Create a Single Index for Multiple Columns?
|