Re: what use of Transaction

Beatrice,

in ORACLE a transaction is defined as a set of DML ststements (ie Sel, Upd, Ins, Del) between COMMIT points.

In your scenario, the rs_tmp rollback statement is being selected to record the 'before images' of any data that is to be updated, up until the next COMMIT ststement.

So, the first DELETE ststement will use the rs_tmp rollback segment.

Once the first COMMIT statement is invoked, so the next statement may use any rollback segment that is on-line.

If you wish to stipulate the rollback segments all the way through your script then you will require either :

1. a SET TRANSACTION USE ROLLBACK SEGMENT statement before each DELETE statement, or
2. remove the COMMITS and treat it all as one transaction. You will need to ensure that the rollback segment is large enough for the whole job.

Hope that this helps.

Regards

Bryan Jones

Beatrice wrote:

> hi, I got a question
>
> under Sql*Plus ,One statement looks like
>
> > Set transaction
> > use rollback segment rs_tmp
> > sta delete.sql
>
> What does these sql statement lines mean here? I understand
> script 'delete.sql'
> I do not know what the relationship between the transaction, rollback
> segment, and sql script?
>
>
> 1.) Here rs_tmp is the name of rollback segment.
>
> 2.) delete.sql looks like:
>
> delete from conv_price_list_items where prli_blgto_prclist_id in
> (select pk_prclist_id from price_lists where status='Removed');
>
> commit;
>
> delete from conv_price_list_items where conv_fl = 'Y' and loaded_dt <
> sysdate -30;
> commit;
>
> delete from conv_products where conv_fl = 'Y' and loaded_dt < sysdate -30;
>
> commit;
> delete from conv_product_struct_items where conv_fl = 'Y' and loaded_dt <
> sysdate -30;
> commit;
>
> thanks.
>
> Bearice.
>
> --
> Posted via CNET Help.com
> http://www.help.com/
Received on Wed Oct 27 1999 - 17:30:02 CDT