1) that should be trivial. taking a measely 2m undo tablespace (create undo tablespace undo_test datafile size 2m) with no "autoextend", all we need to do is:
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2> create table t as select * from all_objects;
Table created.
ops$tkyte@ORA9IR2> update t set object_name = object_name;
update t set object_name = object_name
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_TEST'
looking at dba_extents, we'd see:
ops$tkyte@ORA9IR2> break on segment_name skip 1
select segment_name, extent_id, blocks
from dba_extents
where tablespace_name = 'UNDO_TEST'
order by segment_name, extent_id
/
ops$tkyte@ORA9IR2> 2 3 4 5
SEGMENT_NAME EXTENT_ID BLOCKS
------------------------------ ---------- ----------
_SYSSMU13$ 0 7
1 8
_SYSSMU14$ 0 7
1 8
_SYSSMU15$ 0 7
1 8
_SYSSMU16$ 0 7
1 8
_SYSSMU17$ 0 7
1 8
_SYSSMU18$ 0 7
1 8
_SYSSMU19$ 0 7
1 8
_SYSSMU20$ 0 7
1 8
2 8
3 8
4 8
5 8
6 8
7 8
8 8
9 8
10 8
11 8
12 8
_SYSSMU21$ 0 7
1 8
_SYSSMU22$ 0 7
1 8
31 rows selected.
<b>on segment will steal as many extents as it can, but when there are no more, there are no more.
So</b>
ops$tkyte@ORA9IR2> rollback;
Rollback complete.
ops$tkyte@ORA9IR2> alter session enable resumable;
Session altered.
ops$tkyte@ORA9IR2> update t set object_name = object_name;
<b>and we tail our alert log - shortly we see:</b>
Tue Aug 3 08:22:59 2004
Failure to extend rollback segment because of 30036 condition
statement in resumable session 'User OPS$TKYTE(200), Session 15, Instance 1' was suspended due to
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_TEST'
Failure to extend rollback segment because of 30036 condition
Failure to extend rollback segment because of 30036 condition
...........
<b>so, that shows how to "get there"</b>
2) it will resume when the condition that caused it to pause is corrected or it timesout (you control the timeout).
It really is flagged in dba_resumable as well:
sys@ORA9IR2> select * from dba_resumable;
USER_ID SESSION_ID INSTANCE_ID COORD_INSTANCE_ID COORD_SESSION_ID STATUS
---------- ---------- ----------- ----------------- ---------------- ----------
TIMEOUT START_TIME SUSPEND_TIME RESUME_TIME
---------- -------------------- -------------------- --------------------
NAME
------------------------------
SQL_TEXT
-------------------------------------------------------------------------------
ERROR_NUMBER
------------
ERROR_PARAMETER1
-------------------------------------------------------------------------------
ERROR_PARAMETER2
-------------------------------------------------------------------------------
ERROR_PARAMETER3
-------------------------------------------------------------------------------
ERROR_PARAMETER4
-------------------------------------------------------------------------------
ERROR_PARAMETER5
-------------------------------------------------------------------------------
ERROR_MSG
-------------------------------------------------------------------------------
200 15 1 SUSPENDED
7200 08/03/04 08:22:55 08/03/04 08:22:58
User OPS$TKYTE(200), Session 1
5, Instance 1
update t set object_name = object_name
30036
8
UNDO_TEST
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_TEST'
<b>
In that session I queried -- I then:</b>
sys@ORA9IR2> select file_name from dba_data_files where tablespace_name = 'UNDO_TEST';
FILE_NAME
-------------------------------------------------------------------------------
/home/ora9ir2/oradata/ora9ir2/o1_mf_undo_tes_0jx3c23k_.dbf
sys@ORA9IR2> alter database datafile '/home/ora9ir2/oradata/ora9ir2/o1_mf_undo_tes_0jx3c23k_.dbf'
2 autoextend on next 1m maxsize 2000m;
Database altered.
<b>
and pretty much immediately the alert showed:
</b>
Failure to extend rollback segment because of 30036 condition
Failure to extend rollback segment because of 30036 condition
Tue Aug 3 08:27:51 2004
alter database datafile '/home/ora9ir2/oradata/ora9ir2/o1_mf_undo_tes_0jx3c23k_.dbf'
autoextend on next 1m maxsize 2000m
Tue Aug 3 08:27:51 2004
Completed: alter database datafile '/home/ora9ir2/oradata/ora
Tue Aug 3 08:27:52 2004
statement in resumable session 'User OPS$TKYTE(200), Session 15, Instance 1' was resumed
<b>and the statement finished</b>
3) that would be in the category of "a truly bad idea". end users calling saying "hey, my query has been running for days (out of temp and just hanging there). "my update has gone off the deep end" and so on.
this is a tool a DBA can use to perform long, large operations -- whilst they are monitoring for the out of space condition.