Which type of PL/SQL construct would you use to automatically correct the error resulting from a statement that was suspended due to a space-related problem?
A.
functions
B.
package
C.
procedure
D.
database trigger
E.
anonymous PL/SQL block
Explanation:
Detecting a Suspended Statement
When a resumable statement is suspended, the error is not raised to the client. In order for corrective action to be taken, the Oracle database provides alternative methods for notifying users of the error and for providing information about the circumstances.
When a resumable statement encounters a correctable error, the system internally generates the AFTER SUSPEND system event. Users can register triggers for this event at both the database and schema level. If a user registers a trigger to handle this system event, the trigger is executed after a SQL statement has been suspended. SQL statements executed within an AFTER SUSPEND trigger are always nonresumable and are always autonomous. Transactions started within the trigger use the SYSTEM rollback segment. These conditions are imposed to overcome deadlocks and reduce the chance of the trigger experiencing the same error condition as the statement.
Within the trigger code, you can use the USER_RESUMABLE or DBA_RESUMABLE views, or the DBMS_RESUMABLE.SPACE_ERROR_INFO function to get information about the resumable statements.
Ref.:
Oracle Database 10g: Administration Workshop II. Page: 13-10.