Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> simple transaction question

simple transaction question

From: Chuck Hamilton <chuckh_at_dvol.com>
Date: 1997/08/12
Message-ID: <33f0b6bf.2673584@news.dvol.com>#1/1

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.
Received on Tue Aug 12 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US