Re: SET TRANSACTION .. didn't work

From: Vadim Grepan <kezal_at_mail.ru>
Date: Thu, 16 Nov 2000 11:31:05 +0300
Message-ID: <3A139B49.1464173C@mail.ru>

Hello Mark!

Mark D Powell wrote:

> >
> > CREATE statements issues a COMMIT
> > before and after they run. The first commit
> > terminates the transaction of 'set transaction'.
> >
> > To use the large rbs, you will have to put all
> > the others off-line, start the CREATE INDEX
> > then bring the others back online.
> >
> > --
> >
> > Jonathan Lewis
> > Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
> >
> > Practical Oracle 8i: Building Efficient Databases
> >
> > Publishers: Addison Wesley Longman
> > Book bound date: 8th Dec 2000
> > See a first review at:
> > http://www.ixora.com.au/resources/index.htm#practical_8i
> >
> > Vadim Grepan wrote in message <3A123A53.70BC8D99_at_mail.ru>...
> > >Hello!
> > >
> > > I need to create large interMedia index so I've decided to use
> > >additional rollback segment (
> > > placed on separate datafile) But SET TRANSACTION .. didn't assign
> > >required rollback.
> > > What would it be? Oracle 8.1.6 on SUN Sparc.
> > >
> > >= = = cut = = =
> > >SQL> SET TRANSACTION USE ROLLBACK SEGMENT RBS_INDEX_TMP;
> > >
> > >Transaction set.
> > >
> > >SQL>
> > >SQL> create index NDX_MSG_CONTENT on tbl_msg(msg_CONTENT)
> > > 2 indextype is ctxsys.context
> > > 3 ');
> > >
> > >create index NDX_MSG_CONTENT on tbl_msg(msg_CONTENT)
> > >*
> > >ERROR at line 1:
> > >ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
> > >ORA-20000: interMedia Text error:
> > >DRG-50857: oracle error in dreiiflush
> > >ORA-01562: failed to extend rollback segment number 5
> > >ORA-01237: cannot extend datafile 6
> > >ORA-01110: data file 6: '/dsk1/s1/oradata/EL83/rbs01.dbf'
> > >ORA-19502: write error on file "/dsk1/s1/oradata/EL83/rbs01.dbf",
> > >blockno 2419
> > >= = = cut = = =
> > >
> > >
> > >Rgds, Vadim Grepam
> > >-------------------------------
> > >Moscow, Russia
> > >
> > >

> Vadim, besides the problem Janathan pointed out, I noticed in the
> Oracle error messages that it was the rbs tablespace datafile that ran
> out of room and not the rbs segment so you may need to allocate another
> file or extend the existing file also.
>
> --
> Mark D. Powell  -- The only advice that counts is the advice that
>  you follow so follow your own advice --
>

Thanks. Of course I've seen this error message too :) I need real LARGE "temporary"rollback which I'll drop after creating index. If I increase "permanent" rollback datafile I cannot decrease it back.

Rgds, Vadim Grepan

---

Moscow, Russia Received on Thu Nov 16 2000 - 02:31:05 CST