Re: Allow 2 processes access to same table

Użytkownik "Tom" <tomNOSPAM_at_teameazyriders.com> napisał w wiadomości news:1066823361.82824.0_at_despina.uk.clara.net...
> Hi,
>
> We currently have a need, to drop obsolete data, to allow some sql scripts
to access tables while the application JVM is also
> accessing these tables.
>
> How do i allow both sessions to alter the data in the same tables at the
same time? I know that they will not effect each other as
> the data they will be effecting is seperated by a good few years but how
can i let oracle know this?
>
> I've been looking and think that this is possible with the ISOLATION_LEVEL
parameter in my session but i am not certain?
>
> This work is needed on 8.1.5 and there is not an option to stop the JVM
during this time.
>

According to your examle, you want to delete rows from table XXXX, which haven't been modified since data stored in others table XXXXXXX for that id. There is other application JVM accessing rows in XXXX. How can you be sure that JVM app isn't going to modify row you just want to delete ????
I suggest you to use oracle job mechanism to do work for you. Create a view, which return a rows to be deleted. -- create a view xxxx_to_delete as select id from xxxx minus select id from xxxxxxx where datalastaltered >= 'dd.mm.rrrr' then crate a procedure

• create procedure delete_from_xxxx is begin delete from xxxx where id in xxxx_to_delete; end; then submit a oracle job to execute that procedure every day at 0:00 PM for example, and let Oracle handle locks. As far as i know the delete statetment may lock entire xxxx table, and any other application cannot modify rows in that table until delete finishes with commit changes. -- Noel