OCA-11-Managing Data Concurrency and Undo

Question 1 of 20

1

1. Changes made with an UPDATE statement in a transaction are permanent in the data-base and visible to other users after what occurs?

Select one or more of the following:

A. DBWR flushes the changes to disk.
B. You issue a SAVEPOINT statement.
C. You issue a COMMIT statement.
D. A checkpoint occurs.

Explanation

Question 2 of 20

1

2. Which of the following commands returns an error if the transaction starts with SET
TRANSACTION READ ONLY ?

Select one or more of the following:

A. ALTER SYSTEM
B. SET ROLE
C. ALTER USER
D. None of the above

Explanation

Question 3 of 20

1

3. Guaranteed undo retention can be specified for which of the following objects?

Select one or more of the following:

A. A tablespace
B. A table
C. The database
D. A transaction
E. The instance

Explanation

Question 4 of 20

1

4. Which of the following lock modes permits concurrent queries on a table but prohibits
updates to the locked table?

Select one or more of the following:

A. ROW SHARE
B. ROW EXCLUSIVE
C. SHARE ROW EXCLUSIVE
D. All of the above

Explanation

Question 5 of 20

1

5. Select the statement that is not true regarding undo tablespaces.

Select one or more of the following:

A. Undo tablespaces will not be created if they are not specified in the CREATE
DATABASE command.
B. Two undo tablespaces can be active if a new undo tablespace was specified and the
old one contains pending transactions.
C. You can switch from one undo tablespace to another while the database is online.
D. UNDO_MANAGEMENT cannot be changed dynamically while the instance is running.

Explanation

Question 6 of 20

1

6. To resolve a lock conflict, which of the following methods can you use? Choose all
that apply.

Select one or more of the following:

A. Oracle automatically resolves the lock after a short but predefined time period by
killing the session that is holding the lock.
B. The DBA can kill the session holding the lock.
C. The user can either roll back or commit the transaction that is holding the lock.
D. Oracle automatically resolves the lock after a short but predefined period by killing
the session that is requesting the lock.

Explanation

Question 7 of 20

1

7. Two transactions occur at the wall clock times in the following table. What happens
at 10:05?

Select one or more of the following:

A. Session 2 will wait for session 1 to commit or roll back.
B. Session 1 will wait for session 2 to commit or roll back.
C. A deadlock will occur, and both sessions will hang unless one of the users cancels
their statement or the DBA kills one of the sessions.
D. A deadlock will occur, and Oracle will cancel one of the statements.
E. Neither session is updating the same column, so no waiting or deadlock will occur.

Explanation

Question 8 of 20

1

8. If all extents in an undo segment fill up, which of the following occurs next? Choose
all that apply.

Select one or more of the following:

A. A new extent is allocated in the undo segment if all existing extents still contain
active transaction data.
B. Other transactions using the segment are moved to another existing segment with
enough free space.
C. A new undo segment is created, and the transaction that filled up the undo segment
is moved in its entirety to another undo segment.
D. The first extent in the segment is reused if the undo data in the first extent is
not needed.
E. The transaction that filled up the undo segment spills over to another undo segment.

Explanation

Question 9 of 20

1

9. Which of the following commands returns control to the user immediately if a table is
already locked by another user?

Select one or more of the following:

A. LOCK TABLE HR.EMPLOYEES IN EXCLUSIVE MODE WAIT DEFERRED;
B. LOCK TABLE HR.EMPLOYEES IN SHARE MODE NOWAIT;
C. LOCK TABLE HR.EMPLOYEES IN SHARE MODE WAIT DISABLED;
D. LOCK TABLE HR.EMPLOYEES IN EXCLUSIVE MODE NOWAIT DEFERRED;

Explanation

Question 10 of 20

1

10. Undo information falls into all the following categories except for which one?

Select one or more of the following:

A. Uncommitted undo information
B. Undo information required in case an instance crash requires a roll forward
operation when the instance is restarted
C. Committed undo information required to satisfy the undo retention interval
D. Expired undo information that is no longer needed to support a running transaction

Explanation

Question 11 of 20

1

11. Undo segments are owned by which user?

Select one or more of the following:

A. SYSTEM
B. The user who initiated the transaction
C. SYS
D. The user who owns the object changed by the transaction

Explanation

Question 12 of 20

1

12. The EM Database Express Undo Advisor uses what to recommend the new size of the
undo tablespace?

Select one or more of the following:

A. The value of the parameter UNDO_RETENTION
B. The number of Snapshot too old errors
C. The current size of the undo tablespace
D. The desired amount of time to retain undo data
E. The most recent undo generation rate

Explanation

Question 13 of 20

1

13. Choose the option that is true regarding locks in Oracle Database 12c.

Select one or more of the following:

A. When session 1 has a table locked using the LOCK TABLE...EXCLUSIVE MODE statement,
all DML statements and queries wait until session 1 does a COMMIT or ROLLBACK .
B. When SELECT...FOR UPDATE is performed, the table is locked.
C. The DDL_LOCK_TIMEOUT parameter can be set to TRUE to not return the
ORA-00054 error.
D. The LOCK TABLE statement can include the WAIT clause to specify the number of
seconds to wait for acquiring the lock.

Explanation

Question 14 of 20

1

14. The following table shows the timestamp and actions by two users. Choose the best
option that describes the outcome of the actions.

Select one or more of the following:

A. John’s query will return the same results all three times it is executed as they are
run in the same session.
B. John’s queries run at 10:16 and 10:20 produce the same result, which is different
from the one at 10:14.
C. John’s query run at 10:16 waits until 10:18 to produce results, waiting for the com-
mit to happen.
D. John’s queries run at 10:14 and 10:16 produce the same result, which is different
from the one at 10:20.

Explanation

Question 15 of 20

1

15. Which statement is true regarding the locking behavior of Oracle Database 12c?

Select one or more of the following:

A. Readers block writers.
B. Writers block readers.
C. Readers block writers.
D. Writers do not block readers.

Explanation

Question 16 of 20

1

16. Identify the operation that does not generate redo.

Select one or more of the following:

A. An INSERT statement reading from a global temporary table into a persistent table
B. An INSERT statement reading from a persistent table into a global temporary table
C. Roll back an UPDATE operation
D. Writing undo records during a DML operation

Explanation

Question 17 of 20

1

17. User Maria just called to let you know that the long-running query she runs every
week just received a Snapshot Too Old error. What is the best action you can take?

Select one or more of the following:

A. Tell Maria to rerun the query.
B. Increase Undo Retention.
C. The materialized view used in the query is stale and needs to be refreshed.
D. Increase the undo tablespace size.

Explanation

Question 18 of 20

1

18. Which two statements regarding undo and transactions are true?

Select one or more of the following:

A. Multiple active transactions can write concurrently to the same extent in an
undo segment.
B. Multiple active transactions can write concurrently to the same undo segment.
C. Each transaction acquires an extent in the undo segment and does not share
the extent.
D. Each transaction acquires a segment in the undo tablespace and does not share
the segments.

Explanation

Question 19 of 20

1

19. Which statement ends a transaction?

Select one or more of the following:

A. UPDATE
B. ALTER TABLE
C. ALTER SESSION
D. ALTER SYSTEM

Explanation

Question 20 of 20

1

20. Which statement regarding lock is true?

Select one or more of the following:

A. A developer must lock the row before performing an update to prevent others from
changing the same row.
B. When a row in a table is locked, the table is locked and no other transactions can
update the table.
C. When two sessions try to update the same row at the same time, both sessions fail.
D. When a session tries to update the row already updated by another session, it waits
until the other session does a commit or rollback.

	Created by Jamonero Blanco over 7 years ago

OCA OCA Quiz on OCA-11-Managing Data Concurrency and Undo, created by Jamonero Blanco on 29/01/2017.

OCA-11-Managing Data Concurrency and Undo

Question 1 of 20

1. Changes made with an UPDATE statement in a transaction are permanent in the data-base and visible to other users after what occurs?

Select one or more of the following:

Explanation

Question 2 of 20

2. Which of the following commands returns an error if the transaction starts with SET TRANSACTION READ ONLY ?

Select one or more of the following:

Explanation

Question 3 of 20

3. Guaranteed undo retention can be specified for which of the following objects?

Select one or more of the following:

Explanation

Question 4 of 20

4. Which of the following lock modes permits concurrent queries on a table but prohibits updates to the locked table?

Select one or more of the following:

Explanation

Question 5 of 20

5. Select the statement that is not true regarding undo tablespaces.

Select one or more of the following:

Explanation

Question 6 of 20

6. To resolve a lock conflict, which of the following methods can you use? Choose all that apply.

Select one or more of the following:

Explanation

Question 7 of 20

7. Two transactions occur at the wall clock times in the following table. What happens at 10:05?

Select one or more of the following:

Explanation

Question 8 of 20

8. If all extents in an undo segment fill up, which of the following occurs next? Choose all that apply.

Select one or more of the following:

Explanation

Question 9 of 20

9. Which of the following commands returns control to the user immediately if a table is already locked by another user?

Select one or more of the following:

Explanation

Question 10 of 20

10. Undo information falls into all the following categories except for which one?

Select one or more of the following:

Explanation

Question 11 of 20

11. Undo segments are owned by which user?

Select one or more of the following:

Explanation

Question 12 of 20

12. The EM Database Express Undo Advisor uses what to recommend the new size of the undo tablespace?

Select one or more of the following:

Explanation

Question 13 of 20

13. Choose the option that is true regarding locks in Oracle Database 12c.

Select one or more of the following:

Explanation

Question 14 of 20

14. The following table shows the timestamp and actions by two users. Choose the best option that describes the outcome of the actions.

Select one or more of the following:

Explanation

Question 15 of 20

15. Which statement is true regarding the locking behavior of Oracle Database 12c?

Select one or more of the following:

Explanation

Question 16 of 20

16. Identify the operation that does not generate redo.

Select one or more of the following:

Explanation

Question 17 of 20

17. User Maria just called to let you know that the long-running query she runs every week just received a Snapshot Too Old error. What is the best action you can take?

Select one or more of the following:

Explanation

Question 18 of 20

18. Which two statements regarding undo and transactions are true?

Select one or more of the following:

Explanation

Question 19 of 20

19. Which statement ends a transaction?

Select one or more of the following:

Explanation

Question 20 of 20

20. Which statement regarding lock is true?

2. Which of the following commands returns an error if the transaction starts with SET
TRANSACTION READ ONLY ?

4. Which of the following lock modes permits concurrent queries on a table but prohibits
updates to the locked table?

6. To resolve a lock conflict, which of the following methods can you use? Choose all
that apply.

7. Two transactions occur at the wall clock times in the following table. What happens
at 10:05?

8. If all extents in an undo segment fill up, which of the following occurs next? Choose
all that apply.

9. Which of the following commands returns control to the user immediately if a table is
already locked by another user?

12. The EM Database Express Undo Advisor uses what to recommend the new size of the
undo tablespace?

14. The following table shows the timestamp and actions by two users. Choose the best
option that describes the outcome of the actions.

17. User Maria just called to let you know that the long-running query she runs every
week just received a Snapshot Too Old error. What is the best action you can take?