Category: Oracle

June 19th, 2007 by Mathias

As Pawel showed in his post some details about how the row scn technology works in specific situations isn't as well documented as you'd wish.

I asked Oracle development to clarify the questions I had and the answer back was both interesting and useful. Every kind of update and lock on a row will make the rowscn for the row/block NULL until the updating/locking transaction completes. That is, any kind of lock and update can be identified when retrieving data.

More interesting is that this can be used to implement "skip locked" which is a feature oracle AQ uses, but it is not supported for use by end users. For a table where rowdependencies is defined, this can now be implemented with "where ora_rowscn is NOT NULL". I don't know exactly how and when I'll use it, but it is good to have this tool when the situation arises. I'm sure it'll come in handy soon enough as discussions about the skip locked feature seems to occur almost on a monthly basis.

I'm on a long vacation so I cannot test this until I come home again. I had some time over this mornings, so I figured it would be a good time to show that the blog isn't completely dead, it's just not too active when I have to choose between talking with friends and family I haven't seen in a couple of years and researching Oracle technologies.

Posted in Oracle, SQL

May 31st, 2007 by Mathias

I'll give myself a quota for no more than one rant a month. However, one of my pet peeves is how Oracle licenses the database. I'l probably return to why I dislike the general model in a future rant, but this on e will be specific to partitioning.

To use partitioning, you have to pay for the Enterprise Edition of the database at $40,000 per CPU and then pay an additional $10,000 for the partitioning option. That is a lot of money if all I really need is SE One (at $5,000 per CPU) with Partitioning. Unfortunately, that is not an option.

I know partitioning used to be a fairly exotic feature. That was however over 10 years ago, when few datbases were really large and few SQL statements would benefit from parallel execution. As the average database has grown to be really large and most database servers now are multi CPU (and core) servers, this should now be treated as core database functionality.

In many cases it doesn't matter as large companies has site wide use of Oracle DB technology. It does, however, matter for smaller companies and startup companies. If you were to build a new solution today and market it initially to small companies or even run it as your own ASP, you would not be able to design with partitioning. Later on, your solution will be created and the cost of rearchitecting for partitioning is unacceptable.

Even worse is when you're taking an existing application and trying to move it downstream where SE One has to be used to meet the budgets those implementations have. Why spend a lot of time of a lot of good resources to implement "manual" partitioning?

I think Oracle would benefit from making partitioning included in all licenses. One thing it would do would tie customers closer to Oracle as the cost of converting partitioning from one DB to another is high due to the lack of standard even for the DDL used. I think it would also remove another reason for customers to consider other databases. I think licensing should be such that customers can scale with the database and get started on what they need. The situation today is such that building small and needing partitioning always requires a discussion of "can we replace Oracle in our technology stack".

By having partitioning as an included feature, companies could design for it when they begin designing new applications and get the use of it as they grow. That helps Oracle attract small companies that hopefully grow into large customers over time. It also helps in not getting people convinced that Oracle doesn't scale as using partitioning was too expensive when they started and now it's too costly (or politically impossible) to change it.

Designing manual partitioning schemes is really a waste of my and other Oracle professionals time. So… Please Oracle, it is time to revisit how licensing partitioning is done. It is a feature we need to put in the core toolbox and get people to make better use of the database with it. I hope 11G comes with a new licensing strategy for this. I believe Oracle would make a lot of money on making this an included feature in all editions of the database.

That's the partitioning rant. I know others have similar ideas on this as I've read some recent blogs on partitioning. I love Oracle's partitioning, but I hate having to use it only for the largest customers. I really dislike having to think of and implement manual partitioning, I know it will n ot scale as well as database driven partitioning and I still have to waste my time on it for financial reasons. Even worse is having to discuss if we should avoid Oracle altogether as we cannot use partitioning and then maybe a cheaper database will work just as well for us. I don't want to have to avoid the best database over financial details.

If one database is the best for the job, then I want a licensing model that allows me to start small and pay more as my need grows (meaning using SE One to start with and grow to EE while I design for and with partitioning the whole time).

Posted in DBA, Licensing, Oracle, Partitioning

May 21st, 2007 by Mathias

Reading this post about why the average Oracle IT worker doesn't read blogs got me thinking. Does the average IT worker read blogs? I'm not sure. In what I have observed, it is something a segment does. I'd contend that the average blog reader is more advanced and more thirsty for more knowledge that the average person in the same field. I think it is also something the more internet aware do. Everyone knows about the internet, but how many non blog readers can explain the technologies involved? I have a feeling that most that can are on the blogosphere as readers today.

This is not to say that there aren't really good Oracle Specialists (or IT Specialists for that matter) that doesn't read blogs at all. Some has never looked at newsreaders and stay away because they don't want the hassle of learning one more product, other feel it is a waste of time as most blogs are useless to them, yet others feel that there are good blogs – but they are too hard to find.

I think most will improve and learn interesting things if they start reading blogs, I know I have learned many things I'd never have thought of or heard about if it weren't for blogs. Participating on and reading the best blogs is like an informal study group where you can opt in to learn about the subjects you are interested in.

I think it is that some like reading and learning small segments, while others are happy just doing what they know and hopefully hitting the Oracle docs from time to time when they get stuck. It may be similar to how it is natural for some to look up concepts and syntax in the documentation while others try to avoid the documentation like the plague. Maybe most blog readers are among those who like to read the documentation to learn more. There is a difference between looking things up because you have to and enjoying it because may always learn something new.

What do you think? Is this an awareness issue or is this just one of those things where some people will never learn to like the blog format for their reading and learning?

Posted in Blogging, Oracle

May 18th, 2007 by Mathias

Both Gary and Philip postd working versions. Gary's didn't work for me when I had the same letter more than one time in the string. Philips did work and the need to deal with each letter as if it is unique can of course be questioned. The intent was for a solution that could handle that and as Philip showed adjusting that is as easy as to just add a distinct.

My version is based on not using analytical functions of which I thought connect by was a part. Looking at it in another way, I wanted something that could be adopted to other databases.

My starting version of the solution would be:

   with v    as (select 'ABC' v from dual)
,base as (select substr(v.v,1,1) nv, 1 nn from v
union all
select substr(v.v,2,1) nv, 2 nn  from v
union all
select substr(v.v,3,1) nv, 3 nn  from v)
select a.nv||b.nv||c.nv
from base a, base b, base c
where a.nn not in(b.nn, c.nn)
and b.nn not in (c.nn)

What's not to like? I get to use one with construct that takes the output from another with construct as it's input. :-)
The "base" with gives us a result table that has one letter from the input string on each row. I'm then joining that with itself three times (as I have three letter in the string) to get a cartesian product that holds all cominations. It has the valid combinations (where all three letters are used) and invalid ones (where some letters are used more than one time). The last thing is to remove the invalid combinations. I do this by checking if the number I assign to each value (nn) occurs more than one time in the result. For AAA we would have nn for table alias A, B, and C be 1. I only have to check if each value occurs later on in the column list as if it was equal to an earlier one, the test for that earlier one would detect that the same was used.

So it works… Interesting and satisfying at the same time. However, the union is not too lovable. How can we avoid it? Well, the easy way would of course be to use the connect by trick, but as I intended to not use it I'll have to cheat and use anoter unrelated table (view).

   with v    as (select 'ABC' v from dual)
,base as (select substr( v.v,rownum,1) nv
,rownum nn from v, all_objects
where rownum <= length(v.v))

select a.nv||b.nv||c.nv
from base a
,base b
,base c
where a.nn not in (b.nn, c.nn)
and b.nn not in (c.nn)

I'm usig all_objects which should have more rows than the length of a string you want all combinations. It works, but I still prefer the connect by version if I were to use it. This way it does satisfy the limitation I put up for this.

So while this works, it is not as dynamic as one could want it to be. You need a different SQL if you have a four letter string. It ieasy to change this for that, but it is still annoying to have to have many different ones. An alternative would be to write PL*SQL to generate the SQL and then execute it. While that may be nicer, I wanted a solution that was a single statement.

I did find a way to do it. I'm sure it can be done easier and nicer. Maybe analytic functions would make the SQL faster and easier to understand. However, This way combines XML, with clauses, and dynamic in-line queries. For fancy use of the Oracle database, it's almost optimal… How practical it would be to use this very often and with long strings would have to be tested before getting it into a production scenario.

   with v as (select 'ABC' v from dual)
,base as (select substr(v.v,rownum,1) nv
,rownum nn
from v
where rownum <= length(v.v))       ,sel  as (select 'select ' ||                        substr(max(sys_connect_by_path('a'||
nn||'.nv', '||')),3)||' data' a
from base
connect by prior nn = nn - 1
start with nn = 1)
,frm  as (select 'from ' ||
'base a'||nn, ',')),2) a
from base
connect by prior nn = nn - 1
start with nn = 1)
,whr2  as (select 'a'||(nn-1)||'.nn not in('||
'a'||nn||'.nn', ', ')),2) ||
')' cond
,max(rownum) rn
from base
where nn > 1
connect by prior nn = nn + 1
start with nn = length( v.v)
group by nn
order by nn)
,whr  as (select 'where ' || substr(max(
sys_connect_by_path(cond, ' and '))
,5) a
from whr2
where not rn = length(v.v)
connect by prior rn = rn + 1
start with rn = length( v.v) - 1)
select extractvalue(t.column_value,'/DATA')
from sel a
,frm b
,whr c
'for $root in $vals
return $root/ROWSET/ROW/DATA'
passing xmltype(dbms_xmlgen.getxml(
'with v    as (select ''ABC'' v
from dual)
,base as (select substr(v.v,rownum,1) nv
,rownum nn
from v
where rownum <= length(v.v))'                              ||a.a||' '                              ||b.a||' '                              ||c.a)) as "vals") t; 

The formatting is a bit forced here as I need it to fit in the line size google allows on this blog. I will not explain in detail what I have tried to achieve in this SQL. If you would want me to talk about what I'm doing in a post, leave a comment and I'll try to write it up in a post soon.

In short, I'm generating different pieces of the SQL statement needed in the with clauses and them I'm using that to create the XML document with all these cominations ina single XML document. I'm then using some XML function and XMLTable with an XML Query to get it back out form one row (one XML doc) to multiple rows to get it back to relational data.

The threaad that started me thinking about this was this thread. There are some permutations things there that solves this with pure analytics. But why do this the easy way when you can make it more complicated. Laurent's solution is probably the only one that scales well with longer strings.

I hope you enjoyed this and hopefully even learned something. I know I did, both that Cartesian products can be used in ways I hadn't really thought of and that I need to learn more about analytical functions.

I also learned more about XML function and how to move data in and out of these dynamic in-line queries.

Posted in Challenge, Oracle, SQL, XML

May 14th, 2007 by Mathias

I don't know of a better name for this feature. I think this name descibes the function well, even if it may sound overly ambitious. This whole post will be based on a post by Laurent Schneider where he uses XML features to do a select count(*) from different tables and return the result in one result table. It is like a auto generated number of selects that are unioned together.

You can find Laurent's post here. It's a wonderful piece of SQL to do something most of us would probably not have thought of using a single SQL statement for.

The version Laurent uses is:

   select table_name
'select count(*) c from '||table_name))
,'/ROWSET/ROW/C')) count
from user_tables;

Let's first review what happens in this query. The inner most function is a call to dbms.getxml with a query that will return one row and one value. This sql stateement is created on the fly based on the table name the outer select retrieves from user_tables. The function will of course turn the query result into XML text. The xmltype that wraps the function will convert the XML text into an XML object so XPATH functions can be used. The extractvalue function pulls out the node ROWSET/ROW/C where the two first are given as that is how getxml will return the data. C is the name of the column or in this case the alias assigned in the select statement as the resulting "column" from the count(*) function. The last thing that happens is that the extracted value is converted to a number and it is then returned to the outer select as the result from this function.

The query returns a result set like:

TABLE_NAME                      COUNT

Posted in Oracle, SQL, XML

May 13th, 2007 by Mathias

Instead of having to look at your different alert logs for ORA-00600 errors frequently, why not have them come to you? This post will, as promised, show the Perl code needed to publish a feed with ORA-00600 errors. This is building on the previous two posts that shows how to create an external table for the alert log and how to pull out all information for an ORA-00600 so each fetch retrieves one the data you're interested in.

The feed I'm using is a RSS 2.0 format. All feeds are fairly closely related so you can change it or provide different ones if you want to. You'll have to look up the different formats and then make the changes you want to this code.

The script I have here is a simplified version that removes all the usual dynamic nature and flexibility with parameters I usually have. This is done for brevity and to focus the script on the task at hand – delivering the ORA-00600 errors to your newsreader soon after they occur.

The script is not pretty and it could be written much nicer with use of modules such as XML::RSS. Unfortunately that module has a chain of dependencies that I didn't want to satisfy, so I'm creating the tags the old fashioned hard coded way. If you are to do a lot feeds with Perl, you will want to get that module installed.

require v5.6;
use strict;
use CGI qw(:standard :html3 :netscape);
use CGI::Carp;
use DBI;
use DBD::Oracle qw(:ora_session_modes);


if ( $ENV{TWO_TASK} ) { undef $ENV{TWO_TASK}; }

my $statusRec;
print 'Content-Type: text/xml' . "\n\n";
print '<?xml version="1.0"?>' . "\n";
print '<rss version="2.0">' . "\n";
print ' <channel>' . "\n";
print ' <title>Oracle ORA-00600</title>' . "\n";
print ' <description>ORA 600 errors</description>' . "\n";
print ' <language>en-us</language>' . "\n";
print ' <copyright>Copyright 2007</copyright>' . "\n";
print ' <managingEditor>email@domain</managingEditor>' . "\n";
print ' <webMaster>email@domain</webMaster>' . "\n";
print ' <generator>script name</generator>';

# Create db connection
$DBH = DBI->connect("DBI:Oracle:host=zzz;SID=zzz;port=nnnn", "alertlog", "alertlog",
{ RaiseError => 1, AutoCommit => 0 })
|| die "Could not connect\n\tError: $DBI::errstr";

my $STH = $DBH->prepare("with alert as
(select rownum rn
from alert_log)
select a.text_line date_msg
,b.text_line file_msg
,c.text_line err_msg
from alert a inner join alert b
on a.rn = b.rn - 1
inner join alert c
on b.rn = c.rn - 1
where c.text_line like '%ORA-00600%'");

$STH->execute() || die "Couldn't execute statement: $STH->errstr";

while($statusRec = $STH->fetchrow_hashref())
print " <item>\n";
print " <title>ORA-600 Occured</title>\n";
print " <description>An ORA-00600 has been detected.\n";
print "<p>\n";
print " \n";
print "The following information was logged about the event:\n</p><p>";
print $statusRec->{"DATE_MSG"} . "</p>\n<p>";
print $statusRec->{"FILE_MSG"} . "</p>\n<p>";
print $statusRec->{"ERR_MSG"} . "</p>\n" . "</description>\n";
print " <author>your name</author>\n";
print " </item>\n";


print " </channel>\n";
print "</rss>";

That is all it takes. This is obviously a CGI script that would run in your web server. If you have one with CGI capabilities configured, you'd place the script there and call it with an url like "http://your.domain/cgi-bin/script_name.cgi". Ask your web admin if you don' know where and how to place this script. If you have your own web server and don't know how to do this, leave a comment and I'll try to give you a few quick hints about how to get it working.

I'm afraid the code above will be truncated by google's new blog interface. The code will still be there so just cut and paste it into your favorite code editor and you'll have all the needed code.

You will probably want to change these lines to something different:

$DBH = DBI->connect("DBI:Oracle:host=...snip...,
...and the following nodes...

The first one should point to where you have Oracle installed and the second will be your connection string. If you don't know the port ask your DBA or try the default of 1521. It is the port you have your listener configured to listen on.

There is no guarantee that the code works as is. Every environment is different and the code is clearly not environment proof. If you have problems with it, check your error log for the web server and leave a message if you want my input on what the problem might be.

Enough alert log stuff? Probably. Sorry if you feel I've overdone it and got stuck on this subject for too long. Next post will not even mention the alert log.

If you have APEX or want to wait for 11G where it will be a supported option, you will want to use APEX and pure SQL for this rather than Perl. A good intro to how to do it can be found on It is written by Sean Dillon and looks well written and easy to follow, it can be found here. I've not tried it myself, but it sounds straight forward and anything from the people behind asktom and APEX at Oracle is usually both easy to understand and easy to implement (relative to the subject's complexity).

Posted in Alert Log, Oracle, Perl, RSS, SQL

May 11th, 2007 by Mathias

Time to expand on the last post. As promised this will be about how to get the ORA-00600 information out of an external table for the alert log. We will use the same user and setup as we had in that post.

Lets first review how an ORA-00600 is represented in the alert log.

   Tue Feb  1 02:09:44 2005
Errors in file /path/to/trace/file:
ORA-00600: internal error code, arguments: [1100],
[0x388F0D990], [0x382253888], [], [], [], [], []

The actual arguments does not matter, I took them from a forum post to just have an example. What we care about for what we're doing here is that the format is one line with a date, one line with a reference to a trace file, and one line specifying the error code (ORA-00600) and the needed arguments (for Oracle support).

Thus, we'll need to find the row with the ORA-00600 and match it up with the two previous rows. To do this we'll return to my favorite SQL key word, the with clause. It lets us get all rows, get a generated unique id per row, and know that the rows we look at will be identical as we're now using the same source. In theory, we could end up with the rows in different order if we joined the actual table with itself. Oracle does not guarantee the order you retrieve data in unless you order it, end we have nothing to order on here.

    with alert as (select rownum rn
from alert_log)
select a.text_line date_msg
,b.text_line file_msg
,c.text_line err_msg
from alert a inner join alert b
on a.rn = b.rn - 1
inner join alert c
on b.rn = c.rn - 1
where c.text_line like '%ORA-00600%';

This gives us the alert log message in three columns. We can now process it any way we want. It could be used to create a report or generate an email with your ORA-00600 messages.

However, the actual ORA-00600 shows that we made a mistake when we created the table. It cuts off the text_line after a comma. That is not what we want, we want all data up to the end of the line. So we need to change the definition of the table to make sure we get all of that.

   conn alertlog/alertlog
drop table alert_log;
create table alert_log
(text_line varchar2(255))
organization external
(type oracle_loader
default directory bdump
access parameters(
fields terminated by x'10')
location ('alert_ORCL.log'));

That's better, the default delimiter of comma has been replaced with linefeed (ASCII 10) for UNIX platforms. Replace it with what you need. As long as it is a charcter that will never occur in a text line in your alert log, this will work fine.

Now that we have a select that gets all data for an ORA-00600 in each row it returns, it is easy to operate on that data. This model can of course be used to list other pieces of data from the alert log. One such use could be to report how long time it takes between log switches. That is left as an exercise for the reader.

My next post will show how to use this to create a RSS feed for your ORA-00600. That allows you to within minutes know when you have had an ORA-00600 occur and you get it delivered as a separate feed to your news/blog reader.

Posted in Alert Log, Oracle, SQL

May 7th, 2007 by Mathias

I'm going to discuss how to get SQL access to your alert log in this entry. I will then build on that to make it even more useful in future posts.

Browsing the alert log can be convenient if you're already on the server. However, it is often not possible or at least not overly convenient. You may find that as the developer DBA you do not have access to the production server, or you have to log in to many different servers to check all your alert logs. Since SQL is the main tool for DBAs and one of the most critical for developers of database driven applications, it would make sense to access the key log for database operations and issues via the same interface and be able to use your SQL tool of choice.

The way to be able to access the alert log with SQL is to define an external table for it. So I'll use this need to write a little about how to create an external table for text file. It's pretty easy and can often be very useful.

create directory bdump as '/path/to/bdump';
create user alertlog identified by alertlog;
alter user alertlog quota unlimited on users;
grant create session, resource to alertlog;
grant read,write on directory bdump to alertlog;

The directory path needs to be what you have "background_dump_dest" set to. You can look the current value up in v$parameter. To be able to do this with one script across databases, you'll want to make this more dynamic with pl*sql such that the directory is retrieved from the database and used to build a dynamic DDL statement.

Now that we have a user with access to our bdump directory, we'll create our alert_log table.

create table alert_log
(text_line varchar2(255))
organization external
(type oracle_loader
default directory bdump
location ('alert_ORCL.log'));

This is a minimal create external table statement. There are many more options to use, but we'll leave those alone in this blog post.

The create table statement is pretty straight forward, here's what it will do:

  • Creates a table with data that is located external to the database
  • Take all data in each line into the column text_line
  • Use the normal oracle_loader (SQL*Loader) driver to access the data
  • It uses the bdump directory object to specify where the alert log file is located.
  • Get data from the file alert_ORCL.log.

That is all it takes. We can now access this table with SQL and use our normal techniques for filtering and aggregating information.

To look at the snapshot too old messages we generated in a previous blog post, we'd use this statement:

select text_line
from alert_log
where text_line like '%ORA-01555%';


Posted in Alert Log, DBA, Oracle, SQL

April 29th, 2007 by Mathias

Mutlitable insert is a feature I've read about many times, but I've never taken the time to read up on it or play with the way this SQL statement works. I figure making a post about it will give me the reason I need to learn more about it.

It is not so much a new statement as an extension of the existing INSERT statement. It allows you to insert into different tables and with different conditions from a select statement. That is, it is a variant of the common "insert into select from" version of the insert statement.

There are of course many uses of this. Splitting a table into multiple tables seems like an obvious use, but more common may be when data is added to more than one table at the same time. One such situation could be a situation where a blob is placed in it's own table or where a parent table needs to be maintained while loading the dependent table with new data.

In our test we'll read all rows from all_ind_columns and insert into the tables test_tb and test_col. The two tables will of course hold tables and indexed columns in tables respectively.

Lets first set up the user and the tables.

conn system
create user multiins identified by multiins;
alter user multiins quota unlimited on users;
grant create session, resource to multiins;
conn multiins/multiins
create table test_tb as
select table_name
from user_ind_columns
where 1 = 0;
create table test_col as
select table_name, column_name
from user_ind_columns
where 1 = 0;

We now have a table test_tb that holds just a table_name and a table test_col that holds table_name and column_name. This is a traditional parent child table relationship. The task is now to in one statement load both tables with data from all_ind_columns so we get one row per table in test_tb and one per indexed column in test_col.

when cn = (select min(column_name)
from all_ind_columns a
where a.table_name = tn) then
into test_tb values (tn)
when 1=1 then
into test_col values (tn, cn)
select table_name  tn
,column_name cn
from all_ind_columns;

Pretty nice. This can be used to denormalize tables or just load tables in parallel when the data is in one single data source. It can even be used to have one join pull data and then put the data into different tables. The number of conditions and tables is virtually unlimited. I believe the limit is set to 127 and I'm sure I'll reach the limit for a single statement (64,000 bytes) before I reach that limit.

The subselect used in the first condition is to make sure that we only insert the table name once. In each table, there will just be one column that has the lowest value as column names are unique within a table.

You'd of course want to add primary keys and a foreign key if these tables were to now be used in a real application.

Posted in Oracle, SQL

April 23rd, 2007 by Mathias

The dreaded ORA-01455 hits again. Snapshot to old is easy to understand, but not always easy to fix. In this article I'll show how the misbehaving process may actually be the process that receives the error.

Snapshot too old is the problem we encounter if the block you need to access cannot be found. This doesn't mean that the block has been misplaced by the database, but rather that the version of it that you need is no longer available in the database. This will happen if the block is updated, and the updating session commits and the block in undo is overwritten before you need to access it. This will often happen in environments where long and short transactions are mixed and your undo is not configured correctly.

Lets look at an example of this:

  1. Process A starts reading table XYZ
  2. Process B updates a block in table XYZ
  3. Process B commits.
  4. Process C makes an update to another table
    Oracle reuses the undo block that held the undo information from process B.
  5. Process A reaches the now updated block in table XYZ.
    The block has been updated and Oracle looks in undo for a version of it that existed at the time process A issued the statement (step 1).
  6. As no such block is available in undo anymore, process be receives the ORA-01455.

The solution to that is of course to have process B not commit as often, run process A at some time where this problem will not be triggered, or set up undo so undo is saved long enough for process A to complete it's reading of table XYZ. Of these, the two first may not be an option in all cases and the last requires enough disk space to be available.

In this blog I'll try to see if the process that causes the issue could be the process that receives the 1455. We typically look at other processes for who to blame when this occurs, so it's interesting to see if we maybe should look at the failing process first. After all, that process will be a long running process and it often performs quite a bit of work that generates undo.

Let's first set up a small undo tablespace.

conn system
create undo tablespace small_undo
datafile '/path/to/the/new/datafile' size 5M
autoextend off;
alter system set undo_tablespace = small_undo;

We now have a small tablespace that will cause generated undo to be overwritten pretty soon after it is committed. The next step is to create a test user and create our test table.

conn system
create user smallundo identified by smallundo;
alter user smallundo quota unlimited on users;
grant create session, resource to smallundo;
conn smallundo/smallundo
create table test_tb as
select rownum col_a
,rpad('ABC', 80, 'X') col_b
from dual
connect by level <= 1000000   order by dbms_random.random;

create unique index test_tb_ind on test_tb (col_a);

begin dbms_stats.gather_table_stats(user

The data is placed in the table in a random order to ensure that we can read it by object_id and thereby get the I/O to access block in a random order.

Lets first begin with verifying that we have a testcase that will use more undo than the undo tablespace can hold. We do this by updating each row one at a time like we'll do later and we'll just not commit until all of them are updated. The commit should of course never be reached if the undo tablespace is small enough.

for row in (select /*+ index(test_tb test_tb_ind) */
from test_tb
where col_a > 0
order by col_a) loop
update test_tb
set col_b = lower(col_b)
where rowid = row.rowid;
end loop;

This will of course update the table row by row in a random order and just change the object_name to the lower case version. If it is successful it will just complete, and if it fails we'll get an error to that effect. Here is the output I got.

ORA-30036: unable to extend segment by 8 in undo tablespace
ORA-06512: at line 4

So all undo needed from this operation does not fit in the undo. Lets fix it by committing after every update, that should make this work, right?

for row in (select /*+ index(test_tb test_tb_ind) */
from test_tb
where col_a > 0
order by col_a) loop
update test_tb
set col_b = lower(col_b)
where rowid = row.rowid;
end loop;

ORA-01555: snapshot too old: rollback segment number 14
with name "_SYSSMU14$" too small
ORA-06512: at line 2

What happened here? Was someone else using the database? No, the database is on my computer and I'm the only one accessing it. I just managed to update blocks I needed and then those blocks were overwritten in the undo before the query had accessed that block for the last time. The result is that my own update caused the select to eventually fail.

While this is a very small test case just to prove the problem, we may still have situations where we contribute to the problem. The solution is to size the undo tablespace for what we need to process and size the transaction for what our unit of work is. This does not mean never commit and always commit is just as incorrect. Size it for what your transaction needs to ensure integrity and make sure you consider what a process failure means for restarting. A well sized transaction will result in just restarting the process and it completes what processing it has left.

Before we're done, we need to restore the undo tablespace so your database is useable for normal processing again. First find the undo tablespaces you have defined, change it back to one of those, and drop the one used in this test.

select tablespace_name
from dba_tablespaces
where contents = 'UNDO';
alter system set undo_tablespace = undotbs1;
drop tablespace small_undo including contents and datafiles;

For the above test case, I used version EE on Solaris 10 x86 running in Parallels on Mac OS X. This is just for future tests if Oracle changes something so it still can be proven when and where this occurred. I believe you'll reproduce this on any version of Oracle that is available today. If you're not using undo, you'll just have to adjust the setup to use rollback segments instead.

This test case is very close to what Tom has in his book. It's not the same exact code, but there is no doubt it is testing and proving the same thing. Tom talks about it more in the context of why some developers may commit too often, while I focus more on showing that it can occur with just one process. A 1455 is not necessarily the result of someone else's poorly written code.

Posted in Commit, Oracle