OracleOracleAndy, a relative newcommer to the Oracle blogging community, has a fantastic article on resumable transactions.

Here’s what Andy has to say about them:

For those of you who have not come across this feature of Oracle, it was introduced in 9iR2. It allows you to set up your session so that if a transaction hits a storage problem e.g. out of temp, tablespace is not big enough,or a table reaches max extents, the transaction is suspended. All you have to do is fix the space problem and and transaction will resume. This saves you all the time of waiting for the rollback, adding some space and starting again hoping you’ve got it right this time.

He goes on to explain in great deal how to setup for and use resumable transactions. I didn’t even know you could do this (damn lack of training budget.) Great article Andy!

database, database administration, dba, database development, oracle, sql, plsql, pl/sql

OracleAfter pointing out how not to create auto increment columns in Oracle, I suppose I should point out how to create auto increment columns in oracle.

Many will gripe about this not being a standard feature in Oracle, but when it’s as easy as two more commands after your CREATE TABLE command I can’t see any good reason to use fancy SQL on every insert.

First let’s create a simple table to play with.

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

Table created.

Now we’ll assume we want ID to be an auto increment field. First we need a sequence to grab values from.

SQL> CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

Sequence created.


Now we can use that sequence in an BEFORE INSERT trigger on the table.

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/

Trigger created.

This trigger will automatically grab the next value from the sequence we just created and substitute it into the ID column before the insert is completed.

Now we’ll do some inserts:

SQL> INSERT INTO test (name) VALUES ('Jon');

1 row created.

SQL> INSERT INTO test (name) VALUES ('Bork');

1 row created.

SQL> INSERT INTO test (name) VALUES ('Matt');

1 row created.

SQL> SELECT * FROM test;

ID NAME
---------- ------------------------------
1 Jon
2 Bork
3 Matt

Because the sequence is updated independent of the rows being committed there will be no conflict if multiple users are inserting into the same table simultaneously.

If you need to capture the value of the auto increment column you’ll want to check out my other article Oracle Auto increment Columns – Part 2

Matt has posted a similar method in which he uses a sequence in the insert eliminating the need for the trigger. That will work just as well without the need for the trigger. The only drawback to this method is slightly longer SQL commands.

This article is about how not to create auto increment columns. If you’re looking for a more acceptable way, you’ll want to check out this article.

Matt has posted some code on his blog for accomplishing an auto increment in Oracle. While his solution may seem like an easy way out of creating triggers and sequences, it will fall apart in practical use.

Here’s what Matt suggested:

INSERT INTO table (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, ‘bork’ FROM table);

Basically he is taking the max value currently in the ID column of the table and adding one for the new ID value. Matt mentions on his site that he is not sure how efficient this would be. While it wouldn’t be very efficient, it will probably not be too slow if the ID column has an index on it (which it will automatically have if it is a primary key.)

The bigger problem will come when you have multiple users connected. To illustrate that we’ll create a small test table:

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));
Table created.

Now let’s insert a couple rows in the table:

SQL> INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'bork' FROM test);1 row created.

INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'Jon' FROM test);

1 row created.

SQL> select * from test;

ID NAME
---------- ------------------------------
1 bork
2 Jon

OK, so far so good, the incrementing works. Now let’s open a second session to the database and insert a row.

Session 2
SQL> INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'Matt' FROM test);

This session hangs! If you dig into the data dictionary (or Enterprise Manager) you will find that the first session has a lock on the table that is blocking the second transaction! So session 2 is waiting for session 1 to commit. Let’s do that and see what happens.

Session 1
SQL> commit;Commit complete.

Session 2
INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'Matt' FROM test)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C002948) violated

Now we see the bigger problem. Since the subquery is evaluated before session 1 commits the subquery which is incrementing the ID value based on the MAX(id) is grabbing old data.

So sorry Matt, there’s no bulletproof way around using triggers and sequences for this.

database, database administration, dba, database development, oracle, sql, plsql, pl/sql

OracleBurleson Consulting has some good examples of complex job scheduling.

Of particular interest is this example which causes a job to be run only Monday through Friday:

-- ----------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour, Monday - Friday
-- ----------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1)+6/24,
'trunc(
least(
next_day(SYSDATE - 23/24,''MONDAY''),
next_day(SYSDATE - 23/24,''TUESDAY''),
next_day(SYSDATE - 23/24,''WEDNESDAY''),
next_day(SYSDATE - 23/24,''THURSDAY''),
next_day(SYSDATE - 23/24,''FRIDAY'')
)
,''HH'')',
TRUE,
:instno);
commit;
end;
/

If you’re not familiar with Burleson Consulting’s site dba-oracle.com you probably should be. They have some great articles on Oracle administration. They even have linked to Life After Coffee recently on this same topic.

If you’re looking for more general information on the DBMS_JOB package, check out my article Scheduling Jobs using Oracle’s Job Queue.

database, database administration, database development, oracle

OracleOracle has taken an interesting step, and I hope others will follow.

Blogs.Oracle.com seems to have been created as an Oracle blogroll! Why is this important? Well, for one thing it means Oracle is encouraging their employees to blog, but additionally they are also linking to non-employee blogs.

Here’s the introduction from Blogs.Oracle.com:

Welcome to the Oracle blogging community, where Oracle executives, employees, and non-employees alike exchange views about best practices for using Oracle and industry-standard technologies. This continuous feedback loop helps Oracle stay in touch with the needs of the overall community, so keep those comments coming!

This not only validates what others have been saying regarding the value of blogging in the corporate environment, but also recognizes the role that non-employees play in the big picture.

Check it out at Blogs.Oracle.com. Right now there are a couple dozen employee blogs and fourty-something non-employee blogs. Hopefully they’ll stay on top of adding new folks as they come along.

blogging, information technology, internet, technology, web, web 2.0, web office, blog, database, database administration, database programming, dba, pl/sql, plsql, sql, oracle

« Previous PageNext Page »