What Happens to the Indexes If a Table Is Recovered?
Submitted by: AdministratorIf 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';
<pre>INDEX_NAME TABLE_NAME UNIQUENES
----------------------- --------------------- ---------
SYS_C004141 STUDENT UNIQUE
SYS_C004142 STUDENT UNIQUE
STUDENT_BIRTH_DATE STUDENT NONUNIQUE</pre>
DROP TABLE student;
Table dropped.
Submitted by: Administrator
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';
<pre>INDEX_NAME TABLE_NAME UNIQUENES
----------------------- --------------------- ---------
SYS_C004141 STUDENT UNIQUE
SYS_C004142 STUDENT UNIQUE
STUDENT_BIRTH_DATE STUDENT NONUNIQUE</pre>
DROP TABLE student;
Table dropped.
Submitted by: Administrator
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';
<pre>INDEX_NAME TABLE_NAME UNIQUENES
------------------------------ ---------- ---------
BIN$K47Sg+udQv2tDUW5cWAIrQ==$0 STUDENT UNIQUE
BIN$6WI0gc79QNqLSNGp2H2Q1Q==$0 STUDENT UNIQUE
BIN$9HwZermkRt+9gonHS/klsQ==$0 STUDENT NONUNIQUE</pre>
If you have trouble running the FLASHBACK statement on your Web interface, run it with SQL*Plus.
Submitted by: Administrator
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';
<pre>INDEX_NAME TABLE_NAME UNIQUENES
------------------------------ ---------- ---------
BIN$K47Sg+udQv2tDUW5cWAIrQ==$0 STUDENT UNIQUE
BIN$6WI0gc79QNqLSNGp2H2Q1Q==$0 STUDENT UNIQUE
BIN$9HwZermkRt+9gonHS/klsQ==$0 STUDENT NONUNIQUE</pre>
If you have trouble running the FLASHBACK statement on your Web interface, run it with SQL*Plus.
Submitted by: Administrator
Read Online Oracle Database Job Interview Questions And Answers
Top Oracle Database Questions
☺ | How Oracle Handles Dead Locks? |
☺ | What Is the Relation of a User Account and a Schema? |
☺ | How To Create Tables for ODBC Connection Testing? |
☺ | What Is Input Buffer in SQL*Plus? |
☺ | What Is the Oracle Implicit Cursor? |
Top DB Oracle Categories
☺ | Oracle PL-SQL Interview Questions. |
☺ | Oracle DBA Interview Questions. |
☺ | Oracle D2K Interview Questions. |
☺ | OCI Interview Questions. |
☺ | Oracle RMAN Interview Questions. |