Enable Resumable

As a DBA one thing I've seen quite a few times is tablespaces being maxed out by large data loads, which in turn leads to the data load being rolled back and developers having to start the process all over again.

Here is an example of what I mean, and one way to overcome this situation in Oracle 10g onwards.

Create a Small Tablespace

SQL> create tablespace small_data datafile 'c:\temp\small_data_01.dbf' size 5M;

Tablespace created.

Create a Table in this Tablespace and Insert lots of data into it.

SQL> create table too_big tablespace small_data as (select * from hr.employees);

Table created.

SQL> insert into too_big select * from too_big;

107 rows created.

SQL> /

214 rows created.

SQL> /

428 rows created.

SQL> /

856 rows created.

SQL> /

1712 rows created.

SQL> /

3424 rows created.

SQL> /

6848 rows created.

SQL> /

13696 rows created.

SQL> /
insert into too_big select * from too_big
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TOO_BIG by 128 in tablespace SMALL_DATA

As you can see from the above example, the transaction has failed and there is no turning back.

Now for the solution.

Here we will pick up the task of inserting rows into the too_big table as our tablespace and table itself still exists. However, before doing the insert, we will set a session level parameter.

SQL> alter session enable resumable;

Session altered.

SQL> insert into too_big select * from too_big;

107 rows created.

SQL> /

214 rows created.

SQL> /

428 rows created.

SQL> /

856 rows created.

SQL> /

1712 rows created.

SQL> /

3424 rows created.

SQL> /

6848 rows created.

SQL> /

13696 rows created.

SQL> /

This time around, what happens is that the session basically hangs. To see what's going on, we'll go to another session and run a query.

SQL> column error_msg format a50 word_wrapped
SQL> select session_id, error_msg from dba_resumable;

SESSION_ID ERROR_MSG
---------- --------------------------------------------------
        92 ORA-01653: unable to extend table SYS.TOO_BIG by
           128 in tablespace SMALL_DATA

and there you have it, the session is waiting for us to fix the error. The downside is that no other session will be able to insert rows into the affected tablespace, but it will prevent the primary session from rolling back. So, here is what happens when in the second session, we fix the problem.

SQL> alter database datafile 'c:\temp\small_data_01.dbf' resize 20M;

Database altered.

First Session:

SQL> /

27392 rows created.

SQL>