Re: Rollback segments on the fly in 7.3.2

On Wed, 17 Jul 1996 10:03:58 -0600, Mark Schaefer <mschaefer_at_dsai.com> wrote:

>I'm working on a data loader that will load data into a temporary table
>in Oracle 7.3.2, and then run a PL/SQL procedure to update the current
>table from the temporary table. Unfortunately, I'm running out of
>rollback segment doing this because we have a large number of small
>rollback segments because the vast majority of the transactions are
>queries and not updates. Any updates not coming from the loader are
>usually limited to one record.
>
> What I'm wondering is if I can create a large (preferably unlimited)
>rollback segment and use it for my data loader, and then destroy it when
>I'm done. I know how to grab a rollback segment in my procedure, but in
>order to bring a rollback segment online, we need to take the database
>down.

No you don't need to bring the database down to bring a rollback segment online.

create rollback segment testing storage ( initial 50k next 50k ) tablespace temp;

alter rollback segment testing ONLINE;

If you really want it to be 'unlimited' you can set your compat to 7.3 and use the following:

create tablespace temprollback datafile '/tmp/xxx.dbf' size 50m autoextend on next 5m;

create rollback segment testing tablespace temprollback;

That will create a tablespace that can grow by 5m pieces (upto the amount of disk you have) and create in that a tablespace (use good storage parms). 7.3 removed the limits on extents/segment so the rollback segment can grow. It would be up to you to drop the rollback segment and remove the associated datafile.

>
> One thing is that we're running in 7.2.2 compatability mode. Will
>changing to 7.3.2 compatibility allow us to create a rollback segment on
>the fly?
>
>Thanks for any input!
>Mark Schaefer
>(mschaefer_at_dsai.com)

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Wed Jul 17 1996 - 00:00:00 CEST