Managing Database Resources - OCP: Oracle Database 12c Administrator Certified Professional - page 460

Identifying Resumable Sessions
By default, if a session is enabled for resumable space allocation, the session is identified in
the NAME column of the DBA_ and USER_RESUMABLE views by the username, session ID, and
instance number, as follows:
SQL> select name from user_resumable;
NAME
------------------------------------
User SYS(0), Session 108, Instance 1
SQL>
You can alter the session identifier by issuing the ALTER SESSION command and adding
the NAME clause, as follows:
SQL> alter session enable resumable name 'LNE test';
Session altered.
SQL> select name from user_resumable;
NAME
----------------------------------------------------
LNE test
SQL>
This changed name remains in effect until it's altered by the ENABLE RESUMABLE NAME
command, until resumable is disabled by the session, or until the session ends.
Working with Resumable Operations
Once you've enabled resumable operations, you'll need to monitor and take action on sus-
pended resumable operations. You'll monitor specific views to determine the status of resum-
able operations, and you'll write AFTER SUSPEND triggers and utilize the DBMS_RESUMABLE
supplied package to take action within a session when a suspend occurs.
Additionally, Enterprise Manager reports resumable alerts and provides the mechanism
for resolving resumable space issues.
Views for Monitoring Resumable Space Allocation
The DBA_RESUMABLE and USER_RESUMABLE views contain rows for suspended resumable
statements as well as those that are executing as normal. The key information columns are
described in Table 10.1. The USER_ID column is not included in the USER_RESUMABLE view,
and as with all USER_ views, only the current session information is shown.
The DBA can also use the V$SESSION_WAIT view to catch suspended resumable opera-
tions. The EVENT column will contain a statement indicating that the operation is suspended
and waiting for the error to be cleared.