Review Questions
1. What will be the salary of employee number 189 at the completion of
the following SQL statements?
update emp set salary = 1000 where employee_num = 189;
savepoint save_1;
update emp set salary = salary * 1.1 where employee_num = 189;
savepoint save_2;
update emp set salary = salary * 1.1 where employee_num = 189;
savepoint save_3;
rollback to savepoint save_2;
commit;
update emp set salary = 1500 where employee_num = 189;
savepoint save_4;
rollback to save_4;
commit;
A. 1000
B. 1100
C. 1111
D. 1500
---
Ans: D.
The last ROLLBACK statement rolls back all DML statements since
SAVEPOINT SAVE_4; the
last UPDATE was executed before the SAVEPOINT to SAVE_4, therefore the
change made by
the last UPDATE is unchanged, and the salary remains 1500.
2. Which of the following commands returns an error if the transaction
starts with SET TRANSACTION
READ ONLY?
A. ALTER SYSTEM
B. SELECT
C. ALTER USER
D. SET ROLE
---
Ans: C.
The ALTER USER command changes data, even though it resides in the data
dictionary; no
data in a table can be changed in a READ ONLY transaction.
3. Which of the following commands is most likely to generate an error
message? (Choose two.)
A. ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=MEMORY;
B. ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;
C. ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL SCOPE=MEMORY;
D. ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL SCOPE=SPFILE;
E. ALTER SYSTEM SET UNDO_TABLESPACE=RBS1 SCOPE=BOTH;
---
Ans: A, C.
You cannot dynamically change the parameter UNDO_MANAGEMENT after the
instance has
started. You can, however, change the UNDO_TABLESPACE parameter to
switch to another undo
tablespace while the instance is up and running.
4. Guaranteed undo retention can be specified for which of the following
objects?
A. A tablespace
B. A table
C. The database
D. A transaction
E. The instance
---
Ans: A. Guaranteed undo retention can be set at the tablespace level by
using the RETENTION
GUARANTEE clause with either the CREATE TABLESPACE or ALTER TABLESPACE
command. Only
undo tablespaces can have this attribute.
5. Which dynamic performance view can help you adjust the size of an
undo tablespace?
A. V$UNDOSTAT
B. V$ROLLSTAT
C. V$SESSION
D. V$ROLLNAME
---
Ans A.
When database activity is at its peak, the V$UNDOSTAT view, in
conjunction with the value for
UNDO_RETENTION and DB_BLOCK_SIZE, can be used to calculate an optimal
undo tablespace
size. Also, the Undo Advisor in the EM Database Control can provide the
same optimal
tablespace size in a GUI environment.
6. Which of the following lock modes permits concurrent queries on a
table but prohibits updates
to the locked table?
A. ROW SHARE
B. ROW EXCLUSIVE
C. EXCLUSIVE
D. SHARE ROW EXCLUSIVE
E. SHARE
----
Ans: E.
SHARE mode permits concurrent queries but prohibits updates to the
locked table. SHARE
mode is required to create an index on the table.
7. The highest level at which a user can request a lock is the ________
level.
A. Schema
B. Table
C. Row
D. Block
---
Ans: B.
The highest level at which a user can request a lock is the table level;
the only other lock level
available to a user is a row level lock. Users cannot lock at the block
or schema level.
8. In the following scenario, two different transactions are updating
rows in the same table. What
happens at 11:45? (Choose the best answer.)
A. One of the users calls the DBA who immediately kills one of the
sessions holding the lock.
B. The transactions in both Session 1 and Session 2 are both rolled back
after both sessions
receive an ORA-00060: Deadlock detected while waiting for resource
message, and
the statements in both transactions must be re-executed, but no other
work is lost.
C. Both Session 1 and Session 2 are killed by Oracle with an ORA-00028:
Your session has
been killed message and must redo all other statements executed since
the last COMMIT.
D. Session 1 generates an ORA-00060: Deadlock detected while waiting for
resource
message and rolls back the transaction. The user in Session 2 is then
free to roll back or commit
their transaction.
Session 1 Time Session 2
------------------------------------------------------------------
update employees set salary = 11:29 update employees set
manager =
salary * 1.2 where employee_id 100 where employee_id
= 109;
= 102;
update employees set salary = 11:44 update employees set
manager =
salary * 1.2 where employee_id 100 where employee_id
= 102;
= 109;
? 11:45 ?
-------------
Ans: D.
At 11:45, both sessions are waiting for the row locked by the other
session. Within a short
but predetermined amount of time, Oracle rolls back the statement that
detected the deadlock,
which could be either session and is not dependent on when each of the
transactions started or
attempted to update rows locked by other users.
9. To retrieve the rollback segment name assigned to a transaction, you
can join the dynamic performance
view V$TRANSACTION to which other dynamic performance view?
A. V$ROLLSTAT
B. V$ROLLNAME
C. V$UNDOSTAT
D. V$TRANSACTION_ENQUEUE
---
Ans: B.
The column XIDUSN in the view V$TRANSACTION can be joined with the
column USN in
V$ROLLNAME to retrieve the column NAME in V$ROLLNAME containing the
rollback segment name.
10. Select the statement that is not true regarding undo tablespaces.
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.
---
Ans: A.
If an undo tablespace is not explicitly created in the CREATE DATABASE
command, Oracle
automatically creates one with the name SYS_UNDOTBS.
11. To resolve a lock conflict, which of the following methods can you
use? (Choose two.)
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.
---
Ans: B, C.
Locks are resolved at the user level by either committing or rolling
back the transaction
holding the lock. Also, the DBA can kill the session holding the lock as
a last resort.
12. If all extents in an undo segment fill up, which of the following
occurs next? (Choose all that apply.)
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.
---
Ans: A, D.
If a transaction fills up an undo segment, either a new extent is
allocated for the undo segment
or other extents in the segment are reused if the undo data in those
extents is no longer
needed by other transactions using the same undo segment. Transactions
cannot cross segment
boundaries in an undo tablespace nor can they move to another segment.
13. Which of the following commands returns control to the user
immediately if a table is already
locked by another user?
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;
---
Ans: B.
Regardless of the type of lock requested, NOWAIT is required if you want
the command with
the lock request to terminate immediately if a lock is already held on
the table.
14. Two transactions occur at the wall clock times in the following
table. What happens at 10:05?
Session 1 Time Session 2
update customer set region = 'H' 9:51
where state='WI' and
county='GRANT';
9:59 update
customer set mgr=201
where
state='IA' and
county='JOHNSON';
update customer set region='H' 10:01
where state='IA' and
county='JOHNSON';
10:05 update
customer set mgr=201
where
state='WI' and
county='GRANT';
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.
---
Ans: D.
At 10:01, Session 1 waits for Session 2. At 10:05, a deadlock will
occur; Oracle detects the
deadlock and cancels one of the statements.
15. Undo information falls into all the following categories except for
which 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
---
Ans: B.
Undo information is required for instance recovery, but only to roll
back uncommitted transactions
after the online redo logs roll forward.
16. Undo segments are owned by which user?
A. SYSTEM
B. The user that initiated the transaction
C. SYS
D. The user that owns the object changed by the transaction
----
Ans: C.
Undo segments are always owned by SYS.
17. Undo data in an undo tablespace is not used for which of the
following purposes?
A. Providing users with read-consistent queries
B. Rolling forward after an instance failure
C. Flashback queries
D. Recovering from a failed transaction
E. Restoring original data when a ROLLBACK is issued
---
Ans: B.
The online redo log files are used to roll forward after an instance
failure; undo data is used
to roll back any uncommitted transactions.
18. Which dynamic performance view shows which transactions are assigned
to which undo segment
in the undo tablespace?
A. V$TRANSACTION
B. V$ROLLSTAT
C. V$SESSION
D. V$UNDOSTAT
---
Ans: A.
The dynamic performance view V$TRANSACTION contains the column XIDUSN,
which is the
undo segment number in the current undo tablespace.
19. The user SCOTT runs a query at 8:25 A.M. that receives an ORA-01555:
Snapshot too old error
after running for 15 minutes. An alert is sent to the DBA that the undo
tablespace is incorrectly
sized. At 10:15 A.M., the DBA checks the initialization parameter
UNDO_RETENTION, and its value
is 3600; the parameter is sized correctly. The DBA doubles the size of
the undo tablespace by adding
a second datafile. At 1:15 P.M., the user SCOTT runs the same query and
once again receives an
ORA-01555: Snapshot too old error. What happens next? (Choose the best
answer.)
A. The DBA receives another alert indicating that the undo tablespace is
still undersized.
B. The user SCOTT calls the DBA to report that the query is still
failing.
C. The second datafile autoextends so that future queries will have
enough undo to complete
when there is concurrent DML activity.
D. Resumable Space Allocation suspends the query until the DBA adds
another datafile to the
undo tablespace, and then the query runs to completion.
Ans: B.
Even if the size of the undo tablespace is adjusted after an undo space
problem, only one alert
is sent for each 24-hour period. Therefore, the only way that the
problem will be resolved
promptly is for SCOTT to call the DBA, as the DBA will not receive
another alert until the next
day when another query fails.
20. The EM Database Control Undo Advisor screen uses ___________ to
recommend the new size
of the undo tablespace.
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
----
Ans: D. The Undo Advisor screen uses the desired time period for undo
data retention and analyzes
No comments:
Post a Comment