Re: simple transaction question

Chuck Hamilton wrote:
>
> I want to archive rows from the sys.aud$ table into a table called
> aud$hist. Step 1 is to insert all rows from sys.aud$ into aud$hist.
> Step 2 is to delete the same rows from sys.aud$. Sounds simple enough.
> the script looks like this:
>
> insert into aud$hist (select * from sys.aud$);
> delete from sys.aud$;
> commit;
>
> The problem is that in between steps 1 and 2, new rows are being added
> to the sys.aud$ table by someone else logging in and those rows get
> deleted without ever being copied. How can this happen in a
> transaction oriented RDBMS? I thought that once my transaction began,
> it would see the exact same view of the database regardless of any
> other simultaneous transactions occurring on the system, until I
> either committed or rolled back. Obviously that's not happening. What
> can I do to get Oracle to act the way I think it should?
> --
> Chuck Hamilton
> Sr. DBA
> Keystone Mercy Health Plan
> chuckh_at_dvol.com
>
> Press CTRL+ALT+DEL to continue.

Oracle by default gives statement level consistency. To get transaction level consistency, you may try the statements 'set transaction read only | read write'.

Meanwhile, workaround to your problem (you might have found one by now):

Trap the time before the operation, and work on the rows inserted before this time. You can do this in any tool, pl/sql, sql*plus, pro*c, etc. In sql*plus it would be:

col tmstmp new_value tmstmp
select sysdate tmstmp from dual;
insert into aud$hist (select * from sys.aud$ where timestamp < &tmstmp); delete from sys.aud$ where timestamp < &tmstmp;

Thanks. Received on Thu Aug 14 1997 - 00:00:00 CDT