Richard Niemiec
"Oracle Database 10g Performance Tuning Tips & Techniques"
SQL> insert into hr.employee_search
2 select * from hr.employees;
The user does not receive a message, and it is not clear that the operation has been
suspended. However, in the alert log (managed by the Automatic Diagnostic Repository
as of Oracle Database 11g), the XML message reads as follows:
org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='dw' host_addr='192.168.2.95' module='SQL*Plus'
pid='1843'>
ORA-01653: unable to extend table
HR.EMPLOYEE_SEARCH by 128 in tablespace USERS9
?–
?–
?–
194 Oracle Database 11g DBA Handbook
The DBA receives a pager alert, set up in OEM, and checks the data dictionary view DBA_
RESUMABLE:
SQL> select user_id, instance_id, status, name, error_msg
2 from dba_resumable;
USER_ID INSTANCE_ID STATUS NAME ERROR_MSG
---------- ----------- --------- -------------------- --------------------
80 1 SUSPENDED User HR(80), Session ORA-01653: unable to
113, Instance 1 extend table HR.EMP
LOYEE_SEARCH by 128
in tablespace USERS9
The DBA notices that the tablespace USERS9 does not allow autoextend, and modifies the
tablespace to allow growth:
SQL> alter tablespace users9
2 add datafile '+DATA'
3 size 100m autoextend on;
Tablespace altered.
The user session??™s insert completes successfully, and the status of the resumable operation is
reflected in the DBA_RESUMABLE view:
USER_ID INSTANCE_ID STATUS NAME ERROR_MSG
---------- ----------- --------- -------------------- --------------------
80 1 NORMAL User HR(80), Session
113, Instance 1
The alert log file also indicates a successful resumption of this operation: