Exam 1z0-497 topic 1 question 131 discussion

Actual exam question from Oracle's 1z0-497

Question #: 131
Topic #: 1

Examine the output:
SQL> SELECT index_name, status FROM dba_indexes WHEREstatus='UNUSABLE;

INDEX_NAME STATUS -
--------------------- -----------------

EIND UNUSABLE -
Which two statements about this index are true?

A. It is ignored by the query optimizer.
B. It is not used while the index is being rebuilt.
C. The index cannot be rebuilt, and has to be re-created.
D. The index is automatically rebuilt when used the next time.

Show Suggested Answer

Suggested Answer: AB 🗳️
* Oracle indexes can go into a UNUSABLE state after maintenance operation on the table or if the index is marked as 'unusable' with an ALTER INDEX command.
A direct path load against a table or partition will also leave its indexes unusable.
Queries and other operations agains a table with unuable indexes will generate errors:
ORA-01502: index string.string or partition of such index is in unusable state
* The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:
Indexes:
SELECT 'alter index '||index_name||' rebuild tablespace '||tablespace_name ||';'

FROM dba_indexes -
WHERE status = 'UNUSABLE';

by [deleted] at April 4, 2025, 8:37 a.m.

Comments

Submit Cancel

Currently there are no comments in this discussion, be the first to comment!