You want to prevent a group of users in your database from performing long-running transactions that consume
huge amounts of space in the undo tablespace. If the quota for these users is exceeded during execution of a
data manipulation language (DML) statement, the operation should abort and return an error. However, queries
should still be allowed, even if users have exceeded the undo space limitation.
How would you achieve this?
A.
Specify the maximum amount of quota a user can be allocated in the undo tablespace.
B.
Decrease the number of Interested Transaction List (ITL) slots for the segments on which these users
perform transactions.
C.
Implement a profile for these users.
D.
Implement a Database Resource Manager plan.
I would suggest D because “The Oracle Database Resource Manager can be used to establish user quotas for undo space. The Database Resource Manager directive UNDO_POOL allows DBAs to limit the amount of undo space consumed by a group of users (resource consumer group).
You can specify an undo pool for each consumer group. An undo pool controls the amount of total undo that can be generated by a consumer group. When the total undo generated by a consumer group exceeds its undo limit, the current UPDATE transaction generating the undo is terminated. No other members of the consumer group can perform further updates until undo space is freed from the pool.
When no UNDO_POOL directive is explicitly defined, users are allowed unlimited undo space.”