In the ORCL database, UNDOTBS1 is the active undo tablespace with these properties:
1. A size of 100 MB
2. AUTOEXTEND is off
3. UNDO_RETENTION is set to 15 minutes
4. It has RETENTION GUARANTEE
UNDOTBS1 fills with uncommitted undo 10 minutes after the database opens.
What will happen when the next update is attempted by any transaction?
Given the properties of the undo tablespace, specifically that AUTOEXTEND is off and RETENTION GUARANTEE is enabled, the system is required to preserve all unexpired undo data, even if it results in transaction failures due to lack of space. Since the undo tablespace is filled with uncommitted undo, space cannot be reused for new transactions. Therefore, the next update attempt will fail, returning an error message indicating that the segment cannot be extended in the undo tablespace 'UNDOTBS1'.
It should be B.
i agree
B. To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.
Its a tricky question. To me answer should be C. The undo_retention parameter is ignored for a fixed size undo tablespace. The database may overwrite unexpired undo information when tablespace space becomes low. Go to this link and search this: "The following points explain the exact impact of the UNDO_RETENTION parameter on undo retention" https://docs.oracle.com/cd/B19306_01/server.102/b14231/undo.htm
I think you might be right! Retention Guarantee enforces unexpired undo to be retained for the specified undo retention, but as undo retention is ignored for fixed size undo tablespaces, unexpired undo can be overwritten to support new transactions But my issue with this question is, that it states that the undo tablespace is filled with uncommited undo, which means that it is active undo which will never be overwritten right? Only unexpired & expired undo may be overwritten to support new transations
Please ignore my previous comment, I was just confused at that time Answer C is 100% wrong! While it is true, that the undo retention will be ignored for fixed size undo tablespaces, it won't be ignored if retention guarantee is enabled! Source: https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams222.htm#REFRN10225 For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION !!!unless retention guarantee is enabled.!!! So the correct answer for this question is B!
Tricky question indeed. The documentation clearly shows that: "The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database always tunes the undo retention period for the best possible retention, based on system activity and undo tablespace size." This shows that Unexpired segments will be overwritten by new transactional data. However, the issue here is with the part of the question that states "UNDOTBS1 fills with uncommitted undo 10 minutes after the database opens". This implies that all segments have an ACTIVE state. ASKTOM states: "We will not reuse space (wrap the rollback segment back around over itself) while there is an active transaction there." With this in mind the answer has to be B.
I did not understand why the answer is B?
It does not matter whether the RETENTION GUARANTEE is enabled or not, the UNDO is filled with transactions that are not commited, so space cannot be reused inside the UNDO. Transaction will fail --> answer B
A - false; if UNDO tablespace UNDOTBS1 is active, undo records are stored in it, not in SYSTEM B - true; database cannot extend UNDO tablespace due to AUTOEXTEND OFF and cannot rewrite old UNDO records due to RETENTION GUARANTEE C - false; undo records cannot be overwritten due to UNDO_RETENTION and RETENTION GUARANTEE settings D - false; see A E - false; see C
b right
correct answer is B: The transaction is not committed, therefore the undo space is active. It cannot be overwritten by the next update, so it throws an error.
B. "UNDOTBS1 fills with uncommitted undo 10 minutes after the database opens."...data is uncommitted, so oracle is not going to discard uncommitted data.....
B is correct
in fact it is an interesting question! it is C: The UNDO_RETENTION parameter can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This action can potentially cause some queries to fail with a "snapshot too old" message. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled. https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/UNDO_RETENTION.html#GUID-D238E854-A2C5-463E-A146-31F61300124E
Your quote only applies to unexpired undo space. In our case the undo is active and that cannot be reused by another transaction. C is the wrong answer, B is the correct.