Re: RBSBIG

[Paul Hoyte <phoyte@SHARIDIONNE.COM>]

George:

I would suggest executing the insert/update commands within the same
begin-sql paragraph or set the transaction to use the rbsbig EACH time
just before you execute the insert but within the same begin-sql
paragraph.  Transactions can be assigned different RBSegments so to hold
the one that you want you have to use it right away with the sql
paragraph.  Executing another procedure will create the possibility to
lose the assigned segment.

HTH

Paul Hoyte
Sharidionne Inc.
(248) 559-6868
Email: phoyte@sharidionne.com



-----Original Message-----
From: Discussion of SQR, Brio Software's database reporting language
[mailto:SQR-USERS@list.iex.net] On Behalf Of George Jansen
Sent: Wednesday, July 10, 2002 3:21 PM
To: SQR-USERS@list.iex.net
Subject: Re: RBSBIG


When in doubt, check.

I decided to test this and created a scratch table:

create table scratch(a varchar2(2));

I then ran the followig SQR:

!!!!!!!!!!!!!!!!!!!!
begin-program
  do insert-rows
  do insert-rows
end-program

begin-procedure set-rbs
begin-sql
  set transaction use rollback segment rbsbig;
end-sql
end-procedure

begin-procedure check-rbs ($message)
begin-select
r.name &rname
        display $message noline
        display &rname
from v$transaction t, v$rollname r
where t.xidusn = r.usn
  and t.ses_addr =
        (select distinct s.saddr
         from v$session s, v$mystat m
         where s.sid = m.sid)
end-select
end-procedure check-rbs

begin-procedure random-dml
begin-sql
  insert into scratch(a) values('b');
end-sql
end-procedure random-dml

begin-procedure insert-rows
  do set-rbs
  do random-dml
  do check-rbs('Expect rbsbig, segment is ')
  commit
  do random-dml
  do check-rbs('Segment probably changed: ')
  commit
end-procedure insert-rows !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

and, as expected, saw

Expect rbsbig, segment is RBSBIG
Segment probably changed: R01
Expect rbsbig, segment is RBSBIG
Segment probably changed: R01

Note that you will have to have privileges on the dynamic performance
views to actually run this SQR; however it requires no special
privileges to issue the 'SET TRANSACTION USE ROLLBACK SEGMENT".