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:

  It's really very simple
>
> insert into aud$hist (select * from sys.aud$);
> delete from sys.aud$;

Instead last delete statement you must use

  DELETE FROM sys.aud$ a

     WHERE EXISTS ( SELECT 'x' FROM aud$hist b
                          WHERE a.SESSIONID = b.SESSIONID  AND
                                a.ENTRYID = b.ENTRYID      AND
                                a.STATEMENT = b.STATEMENT  AND
                                a.TIMESTAMP = b.TIMESTAMP );

> commit;

You can modify WHERE clause for be more sure in success of operation.

>
> 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?

  But aud$ is table of system Data Dictionary and locks on it can be a reason of crush or stop of system in whole.

> --
> Chuck Hamilton
> Sr. DBA
> Keystone Mercy Health Plan
> chuckh_at_dvol.com
>
> Press CTRL+ALT+DEL to continue.
 

-- 

With best regards,
                      Kir

+----------------------------------------------------------------------+
|-=-[   Igor Kirasirov   ]=-=-=-=-=-=-=-=-[ kir_at_aix.krid.crimea.ua ]=-=|
|=-=[ Simferopol, Crimea ]-=-=-=-=-=-=-=-=[  voice (0652)52.10.32  ]-=-|
+----------------------------------------------------------------------+