Users report this error message when inserting rows into the orders table:
ERROR atline1:
ORA-01654f:unable to extend index USERS.ORDERS_IND by 8in tablespace INDEXES
You determine that the indexes tablespace is out of space and there is no free space on the
filesystem used by the Oracle database.
Which two must you do to fix this problem without affecting currently executing queries?
A.
drop and re-create the index
B.
coalesce the orders„ind index
C.
coalesce the indexes tablespace
D.
perform an on line table rebuild using dbns_redefir.ition.
E.
rebuild the index online moving it to another tablespace that has enough free space for the
index
A and C is fine
B,E
How can you perform coalesce the indexes tablespace?
If you do not have the space required to rebuild an index, you can choose instead to coalesce the index. Coalescing an index is an online operation.
Agree with B and E
Thinking better .
It’s C and E
First you rebuild the index online moving it to another tablespace that has enough free space for the index
and then you
coalesce the indexes tablespace
I Agree C and E
I would do C and E but I think the answet theyre expecting its B and E .
See here:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes004.htm#i1006864
BE
I would go through:
1. (B) Alter Index USERS.ORDERS_IND Coalesce;
then
2. (E) Alter index Rebuild to another Tablespace that has an enough freespace.
BD
Improper sizing or increased growth can produce index fragmentation. To eliminate or reduce fragmentation, you can rebuild or coalesce the index.
B – beacuse
Quickly frees up index leaf blocks for use.
E – for sure
But if You rebuild index there is no sense to run coalesce on index – because index has been already rebuilded / cleaned up
Maybe C
COALESCE on Tablespace
For each data file in the tablespace, this clause combines all contiguous free extents
into larger contiguous extents.
But this does NOT free any space
No idea
BE