Re: SET TRANSACTION .. didn't work

In article <974275242.20019.0.nnrp-07.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> 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 --


Sent via Deja.com http://www.deja.com/
Before you buy.