Exam 1z0-062 topic 2 question 2 discussion

Actual exam question from Oracle's 1z0-062

Question #: 2
Topic #: 2

One of your databases supports an OLTP workload.
The default UNDO tablespace is fixed size with:
1. RETENTION NOGUARANTEE
2. UNDO_RETENTION is 12 minutes
User SCOTT gets this error after a query on the SALES table has run for more than 15 minutes:

ORA-01555: snapshot too old -
Which three factors taken separately or in some combination might be the cause? (Choose three.)

A. An update was made to the SALES table after the query began
B. An update to the SALES table was committed after the query began
C. A committed delete to the SALES table was made more than 12 minutes before the query began
D. An uncommitted update to the SALES table was made more than 12 minutes before the query began
E. A committed update to the SALES table was made more than 12 minutes before the query began
F. An uncommitted delete to the SALES table was made more than 12 minutes before the query began
G. An update was made to the SALES table before the query began

Show Suggested Answer

Suggested Answer: ADE 🗳️

by SKY_ming at March 14, 2020, 12:16 p.m.

Comments

Submit Cancel

hggz

Highly Voted 5 years, 3 months ago

I think ACE is correct * A - new transaction need space * CE - old commited transactions after retention time

upvoted 8 times

badguy001djh

4 years, 3 months ago

I think CE is incorrect, because transaction is commited before query beginning.

upvoted 2 times

...

Hacova

Highly Voted 3 years, 11 months ago

I think A,D,F is correct.

upvoted 5 times

...

badguy001djh

Most Recent 4 years, 3 months ago

1.Some change(either before or after query beginning) of SALES table data must be commited when query executing. 2.Other transactions consume undo space results in overwriting undo generated by transaction in 1 when query executing. 3.Query needs to revert change made in 1,but undo generated by transaction in 1 has been overwrited, and then ora-01555 shows up. So CE is inccorrect, their transactions are commited when the query has not started yet. I think ABF is true.The update commited in option B is the update made in option F.

upvoted 2 times

8111

1 year, 10 months ago

why is not D？

upvoted 1 times

...

dancymonkey

4 years, 11 months ago

The default undo behavior is to overwrite the undo information of committed transactions that has not yet expired rather than to allow an active transaction to fail because of lack of undo space. This behavior can be changed by guaranteeing retention. With guaranteed retention. undo retention settings are enforced even if they cause transactions to fail. Uncommitted (Active) -> Never overwritten Committed (Unexpired) -> less than retention period 12 mins -> retained -> but NOGUARANTEE Old committed (Expired) -> older than retention period 12 mins -> overwritten by new transactions I think CE for Old committed and A is for Committed so ACE Nothing to do with uncommitted because never overwritten

upvoted 4 times

...