Resumable Storage Allocation

At the moment I’m writing a short 9i/10g New Features seminar for the developers at work. The idea is to run through lots of new features very quickly to see if anything lights their candle and they can then go off and investigate further afterwards.

It occurred to me while I was putting some slides together that 9i’s resumable storage allocation feature is under-used so I thought I’d blog about it here. It’s amazing how many times this facility would have helped us avoid big problems at work.

I also realised, and not for the first time, that what differentiates the best people I’ve worked alongside from the rest is that they realise how easy it is to knock up a quick demonstration of a feature to make sure it works as expected and show it to others. I wasn’t sure of how the contents of one of the views would look so I tried it out. Of course, you need a play database for this (but everyone should have at least one of those kicking around) but this entire demo took 10 minutes to put together, even allowing for initial head-scratching, typos etc. I’d far rather spend 10 minutes playing than asking different people and surfing the net. The hands-on aspect makes the information ‘stick’ better and you might come across quirks you weren’t expecting. Anyway, here’s the example

TEST1020> create tablespace resumable_test
  2  datafile '/u01/oradata/TEST1020/resumable_test01.dbf' size 1m;

Tablespace created.

TEST1020> alter user testuser quota unlimited on resumable_test;

User altered.

TEST1020> grant resumable to testuser;

Grant succeeded.

TEST1020> connect testuser/testuser
Connected.
TEST1020> create table res tablespace resumable_test as select * from all_objects;

Table created.

TEST1020> select segment_name, bytes from user_segments where segment_name = 'RES';

SEGMENT_NAME              BYTES
-------------------- ----------
RES                      524288

TEST1020> alter session enable resumable;

Session altered.

TEST1020> insert into res select * from res;

This session will hang because it’s filled up the tablespace. You can see this in another DBA session

TEST1020> select user_id, status, sql_text, error_msg
  2  from dba_resumable
  3  /

   USER_ID STATUS
---------- ---------
SQL_TEXT
--------------------------------------------------------------------------------
ERROR_MSG
--------------------------------------------------------------------------------
        27 SUSPENDED
insert into res select * from res
ORA-01653: unable to extend table TESTUSER.RES by 8 in tablespace RESUMABLE_TEST

TEST1020> alter database datafile '/u01/oradata/TEST1020/resumable_test01.dbf' resize 10m;

Database altered.

TEST1020> select user_id, status, sql_text, error_msg
  2  from dba_resumable
  3  /

   USER_ID STATUS
---------- ---------
SQL_TEXT
--------------------------------------------------------------------------------
ERROR_MSG
--------------------------------------------------------------------------------
        27 NORMAL

And if we go back and look at the TESTUSER session, the statement has completed

TEST1020> insert into res select * from res;

4490 rows created.

TEST1020>

Full documentation for 9i is here.