Re: SET TRANSACTION .. didn't work

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

>

>