Thursday, February 18, 2016

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

Friday, February 12, 2016

SELECTs Do Not Block UPDATEs

I went on my first DBA course in 1997 and the lecturer there explained that readers do not block writers in an Oracle database. I had an issue recently which appeared to contradict this so I have reproduced it below in an Oracle 11.2.0.1 database.
 
I noticed in OEM that there was some issue in the database. As usual, click on the images to enlarge them and bring them into focus if you need to:


I looked at the Blocking Sessions screen and saw that Andrew was blocking Fred:


I clicked on the link to see the SQL which Fred was running:


I clicked on the link to see the SQL which Andrew was running:


So it seemed that: 

SELECT COUNT(*)
FROM DBA_TABLES
 
was blocking:
 
update andrew.tab1
set col1 = 2
where col1 = 1
 
As you might have guessed, I set this test up on purpose and this is what really happened. Andrew created a table:
 
SQL> conn andrew/reid
Connected.
SQL> create table tab1 (col1 number)
  2  /
 
Table created.
 
SQL>
 
He then allowed Fred to update it:
 
SQL> grant update on tab1 to fred
  2  /
 
Grant succeeded.
 
SQL>
 
He added a row to the table, saved it and updated it, setting up a lock in the process:
 
SQL> insert into tab1
  2  values (1)
  3  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> update tab1
  2  set col1 = 2
  3  where col1 = 1
  4  /
 
1 row updated.
 
SQL>
 
Finally, he counted the rows in DBA_TABLES again and again:
 
SQL> declare
  2    row_count number;
  3  begin
  4    while (1=1) loop
  5    select count(*) into row_count
  6    from dba_tables;
  7    end loop;
  8  end;
  9  /
 
Fred then came along and tried to update the same row in TAB1 but had to wait:
 
SQL> conn fred/bloggs
Connected.
SQL> update andrew.tab1
  2  set col1 = 2
  3  where col1 = 1
  4  /
 
So, what is the point of this example?
 
If you look at OEM’s Blocking Sessions screen and see a user blocking another user, the SQL shown against the blocking user is the SQL he/she is currently running. This may or may not be the cause of the lock which is holding up the user(s) underneath.