Creating Tables in the UNDO Tablespace??

I was reading an article written by Martin Widlake in Oracle Scene Issue 58 (Autumn/Winter 2015). It said:

The second new item is the UNDO tablespace. This is a special tablespace that is only used for internal purposes and one that users cannot put any tables or indexes into.

This seemed perfectly reasonable so I wondered what might happen if I tried to do it. In an Oracle 9.2.0.7 database Oracle returned an error:

SQL> create table tab1

  2  (col1 number)

  3  tablespace undo_1

  4  /

create table tab1

*

ERROR at line 1:

ORA-30022: Cannot create segments in undo tablespace

SQL>

In an Oracle 11.2.0.1 database, I was not allowed to use the UNDO tablespace as a user’s default tablespace:

SQL> l

  1  create user andrew

  2  identified by reid

  3* default tablespace undotbs1

SQL> /

create user andrew

*

ERROR at line 1:

ORA-30033: Undo tablespace cannot be specified as

default user tablespace

SQL>

... but I was allowed to create a table in it:

SQL> create table tab1

  2  (col1 number)

  3  tablespace undotbs1

  4  /

Table created.

SQL> 

Does anybody know if this is a bug? I will update this post if I find out.

Postscript written on 19^th February 2016:

I wrote the post above yesterday and, if you check the comments below, you will see that a couple of people have now helped me to understand what happened. It was all down to deferred segment creation. I have discussed this before here and here (and other places too) but it still catches me out from time to time. I returned to the Oracle 11.2.0.1 database and ran Andrzej’s SQL for confirming that UNDOTBS1 was an UNDO tablespace:

SQL> select contents from dba_tablespaces

  2  where tablespace_name = 'UNDOTBS1'

  3  /

CONTENTS

---------

UNDO

SQL>

Then I confirmed that deferred segment creation was turned on:

SQL> l

  1  select value from v$parameter

  2* where name = 'deferred_segment_creation'

SQL> /

VALUE

--------------------

TRUE

SQL>

I created another table in the UNDO tablespace:

SQL> create table dom (col1 number)

  2  tablespace undotbs1

  3  /

Table created.

SQL>

That worked but, as Dom suggested, when I tried to insert a row, Oracle needed to create a segment and was unable to do so:

SQL> insert into dom values (1)

  2  /

insert into dom values (1)

            *

ERROR at line 1:

ORA-30022: Cannot create segments in undo tablespace

SQL>

Finally, I turned deferred segment creation off:

SQL> l

  1  alter session

  2* set deferred_segment_creation = false

SQL> /

Session altered.

SQL>

Once I had done that, I was unable to create a table in the UNDO tablespace at all:

SQL> create table andrzej (col1 number)

  2  tablespace undotbs1

  3  /

create table andrzej (col1 number)

*

ERROR at line 1:

ORA-30022: Cannot create segments in undo tablespace

SQL>

I’m guessing that when Andrzej created his example, deferred_segment_creation was set to false at the session level as above, at the system level like this:

SQL> alter system set deferred_segment_creation = false

  2  /

System altered.

SQL>

Or via an initialisation parameter in his database’s parameter file.