Chapter 6: Monitoring Space Usage 193
Three types of space management problems can be addressed with Resumable Space Allocation:
Out of space in the tablespace
Maximum extents reached in the segment
Space quota exceeded for a user
The DBA can automatically make statements resumable by setting the initialization parameter
RESUMABLE_TIMEOUT to a value other than 0. This value is specified in seconds. At the session
level, a user can enable resumable operations by using the ALTER SESSION ENABLE RESUMABLE
command:
SQL> alter session enable resumable timeout 3600;
In this case, any long-running operation that may run out of space will suspend for up to 3600
seconds (60 minutes) until the space condition is corrected. If it is not corrected within the time
limit, the statement fails.
In the scenario that follows, the HR department is trying to add the employees from the branch
office EMPLOYEES table to an EMPLOYEE_SEARCH table that contains employees throughout the
company. Without Resumable Space Allocation, the HR user receives an error, as follows:
SQL> insert into employee_search
2 select * from employees;
insert into employee_search
*
ERROR at line 1:
ORA-01653: unable to extend table HR.EMPLOYEE_SEARCH by 128
in tablespace USERS9
After running into this problem many times, the HR user decides to use Resumable Space
Allocation to prevent a lot of rework whenever there are space problems in the database, and
tries the operation again:
SQL> alter session enable resumable timeout 3600;
Session altered.
Pages:
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350