OCP-043 alter index..coalesce

55. Users in your production database complain that they are getting the following error message while
trying to insert rows into the ORDERS table:
ERROR at line 1:
ORA01654:
unable to extend index USERS.ORDERS_IND by 8 in tablespace INDEXES
While investigating, you find that the INDEXES tablespace has run out of space and there is no more free
space on the disk where the data files are available.
Which two actions could you perform to overcome this error without affecting the queries that are currently
being executed? (Choose two.)
A.drop and recreate
the index
B.coalesce the ORDERS_IND index
C.coalesce the INDEXES tablespace

D.drop and recreate
the ORDERS table
E.rebuild the index online and move it to another tablespace
Answer: BE

shrink_clause

Use this clause to compact the index segments. Specifying ALTER INDEX ... SHRINK SPACE COMPACT isequivalent to specifying ALTER INDEX ... COALESCE.

COALESCE Clause

Specify COALESCE to instruct Oracle Database to merge the contents of index blocks where possible to free blocks for reuse.

Restrictions on Coalescing Index Blocks Coalescing of index blocks is subject to the following restrictions:

• You cannot specify this clause for an index on a temporary table.

• Do not specify this clause for the primary key index of an index-organized table. Instead use the COALESCE clause of ALTER TABLE.