Re: changing the isolation level

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 04 Feb 2007 09:13:04 -0800
Message-ID: <1170609184.282029@bubbleator.drizzle.com>

Marten Lehmann wrote:

> But if I'm
> putting everything in a staging table, commit the transaction (so the
> other process can read the data in the staging table) and if I detect
> errors then, I don't have that option to rollback.

Nonsense. Sorry but absolute nonsense. I can think of at least a half-dozen ways to do it.

> And then there is no way back to the original data.

Again nonsense. What you are demonstrating is a profound lack of knowledge of Oracle and little else. Allow me to demonstrate just one of several techniques.

SQL> CREATE TABLE t AS SELECT * from all_objects;

Table created.

SQL> UPDATE t

2 SET owner = 'MORGAN'
3 WHERE rownum < 11;

10 rows updated.

SQL> UPDATE t

2 SET created = SYSDATE- 4
3 WHERE rownum < 6;

5 rows updated.

SQL>
SQL> UPDATE t

   2 SET object_id = 1
   3 WHERE object_id IN (
   4 SELECT MAX(object_id)
   5 FROM t);

1 row updated.

SQL> conn / as sysdba
Connected.
SQL> SELECT undo_sql

   2 FROM flashback_transaction_query
   3 WHERE table_owner = 'UWCLASS'
   4 AND table_name = 'T';

UNDO_SQL

update "UWCLASS"."T" set "OBJECT_ID" = '56935' where ROWID = 'AAAN5nAAGAAAB5CAAy
';

update "UWCLASS"."T" set "CREATED" = TO_DATE('07-FEB-06', 'DD-MON-RR') where ROW
ID = 'AAAN5nAAGAAABtqAAE';

update "UWCLASS"."T" set "CREATED" = TO_DATE('07-FEB-06', 'DD-MON-RR') where ROW
ID = 'AAAN5nAAGAAABtqAAD';

update "UWCLASS"."T" set "CREATED" = TO_DATE('07-FEB-06', 'DD-MON-RR') where ROW
ID = 'AAAN5nAAGAAABtqAAC';

UNDO_SQL

update "UWCLASS"."T" set "CREATED" = TO_DATE('07-FEB-06', 'DD-MON-RR') where ROW
ID = 'AAAN5nAAGAAABtqAAB';

update "UWCLASS"."T" set "CREATED" = TO_DATE('07-FEB-06', 'DD-MON-RR') where ROW
ID = 'AAAN5nAAGAAABtqAAA';

update "UWCLASS"."T" set "OWNER" = 'PUBLIC' where ROWID = 'AAAN5nAAGAAABtqAAJ';
update "UWCLASS"."T" set "OWNER" = 'PUBLIC' where ROWID = 'AAAN5nAAGAAABtqAAI';
update "UWCLASS"."T" set "OWNER" = 'SYS' where ROWID = 'AAAN5nAAGAAABtqAAH'; update "UWCLASS"."T" set "OWNER" = 'PUBLIC' where ROWID = 'AAAN5nAAGAAABtqAAG';

UNDO_SQL

update "UWCLASS"."T" set "OWNER" = 'SYS' where ROWID = 'AAAN5nAAGAAABtqAAF'; update "UWCLASS"."T" set "OWNER" = 'PUBLIC' where ROWID = 'AAAN5nAAGAAABtqAAE';
update "UWCLASS"."T" set "OWNER" = 'SYS' where ROWID = 'AAAN5nAAGAAABtqAAD'; update "UWCLASS"."T" set "OWNER" = 'PUBLIC' where ROWID = 'AAAN5nAAGAAABtqAAC';
update "UWCLASS"."T" set "OWNER" = 'SYS' where ROWID = 'AAAN5nAAGAAABtqAAB'; update "UWCLASS"."T" set "OWNER" = 'SYS' where ROWID = 'AAAN5nAAGAAABtqAAA';

16 rows selected.

Which transaction can't you undo?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sun Feb 04 2007 - 11:13:04 CST