-3

I have a problem to solve which requires undo operation of each executed sql file in Oracle Database.

I execute them in an xml file with MSBuild - exec command sqlplus with log in and @*.sql.

Obviously rollback won't do, because it can't rollback already commited transaction.

I have been searching for several days and still can't find the answer. What I learned is Oracle Flashback and Point in Time Recovery. The problem is that I want the changes to be undone only for the current user i.e. if another user makes some changes at the same time then my solution performs undo only on user 'X' not 'Y'.

I found the start_scn and commit_scn in flashback_transaction_query. But does it identify only one user? What if I flashback to a given SCN? Will that undo only for me or for other users as well? I have taken out

select start_scn from flashback_transaction_query WHERE logon_user='MY_USER_NAME'

and

WHERE table_name = "MY_TABLE NAME"

and performed

FLASHBACK TO SCN"here its number"

on a chosen operation's SCN. Will that work for me?

I also found out about Point in Time Recovery but as I read it makes the whole database unavailable so other users will be unable to work with it.

So I need something that will undo a whole *.sql file.

user1284257
  • 27
  • 2
  • 2
  • 1
    In my opinion, if one has to use the flashback feature, or something like this as part of the "usual" operation, something is seriously wrong... Rolling back would be the thing to do. There must be a way to validate your results before committing... (In theory you could create a multiversion schema, and store the new versions of the rows created by the operations of the user by adding a session ID field and a timestamp, so you could revert the effects of a session without affecting others, but that would pose _far_ more problems than it would solve...) – ppeterka Jan 14 '13 at 13:50
  • How should the "only for one user" work? Do you by chance mean schema, rather than (concurrent) users connected to the same schema? –  Jan 14 '13 at 14:26
  • I mean concurrent users performing operations on the same table. Undo should work only for me. – user1284257 Jan 14 '13 at 14:49

2 Answers2

0

This is possible but maybe not with the tools that you use. sqlplus can rollback your transaction, you just have to make sure auto commit isn't enabled and that your scripts only contain a single commit right before you end the sqlplus session (if you don't commit at all, sqlplus will always roll back all changes when it exits).

The problems start when you have several scripts and you want, for example, to rollback a script that you ran yesterday. This is a whole new can of worms and there is no general solution that will always work (it's part of the "merge problem" group of problems, i.e. how can you merge transactions by different users when everyone can keep transactions open for as long as they like).

It can be done but you need to carefully design your database for it, the business rules must be OK with it, etc.

To general approach would be to have a table which contains the information which rows were modified (= created,updated,deleted) by the script plus the script name plus the time when it was executed.

With this information, you can generate SQL which can undo the changes created by a script. To fill such a table, use triggers or generate your scripts in such a way that they write this information as well (note: This is probably beyond a "simple" sqlplus solution; you will have to write your own data loader for this).

Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
  • And you need to make sure the script does not contain and DDL (including `truncate`) –  Jan 14 '13 at 14:25
  • As far as I Know sqlplus commits every file once it finishes executing it. So it's not simple to undo changes by rollback. – user1284257 Jan 14 '13 at 15:00
  • Whether sqlplus will do a commit at exit is a configuration option. http://stackoverflow.com/questions/1368092/why-does-sqlplus-commit-on-exit – Aaron Digulla Jan 14 '13 at 15:05
  • Your main problem with sqlplus is to talk to the tool while it processes scripts. You should consider using a different tool, maybe something written in .NET or Java that can process several scripts and commit/rollback when you want. The `SAVEPOINT` statement might be interesting, too. – Aaron Digulla Jan 14 '13 at 15:07
  • The assumption is that the given sql files will be commited. So I can't do rollback after they are commited. Let's assume that I execute my sql files on Friday and after for example few next days I want to withdraw only my changes. I've been thinking about copying results to another table and then after for example one week undo my own changes leaving what other users have commited. – user1284257 Jan 15 '13 at 12:04
  • Okay, in this case, you will have to use db triggers or your own custom load tool. The triggers/tool can then write audit information into a special undo/audit table. – Aaron Digulla Jan 15 '13 at 12:32
  • As you probably noticed by now, SQL databases aren't very well suited for this kind of work. A NoSQL database with version support might be better. Or you could load your data into a NoSQL database and then have a transformer load the merged result from the NoSQL database into the real database. – Aaron Digulla Jan 15 '13 at 12:34
0

Ok I solved the problem by creating a DDL and DML TRIGGER. The first one takes "extra" column (which is the DDL statement you have just entered) from v$open_cursor and inserts into my table. The second gets "undo_sql" from flashback_transaction_query which is the opposite action of your DML action - if INSERT then undo_sql is DELETE with all necessary data.

Triggers work before DELETE,INSERT (DML) on specific table and ALTER,DROP,CREATE (DDL) on specific SCHEMA or VIEW.

user1284257
  • 27
  • 2
  • 2