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 19th 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.
