Home » SQL & PL/SQL » SQL & PL/SQL » Extracting undo/redo via logminer for one specific transaction?
Extracting undo/redo via logminer for one specific transaction? [message #271900] Wed, 03 October 2007 05:56 Go to next message
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
Member
I want to run a series of updates, deletes and inserts in a transaction and at the end of this transaction I want to use logminer to get the undo/redo entries for the transaction. And only this transaction.

So the questions is:
How can I identify this transaction in v$logmnr_contents?

Regards,
Rolf
Re: Extracting undo/redo via logminer for one specific transaction? [message #271921 is a reply to message #271900] Wed, 03 October 2007 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle version?

Regards
Michel
Re: Extracting undo/redo via logminer for one specific transaction? [message #271929 is a reply to message #271921] Wed, 03 October 2007 06:53 Go to previous messageGo to next message
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
Member
Michel Cadot wrote on Wed, 03 October 2007 13:44
Oracle version?


Its 10.2.0.1.0 (10g)
Re: Extracting undo/redo via logminer for one specific transaction? [message #271932 is a reply to message #271929] Wed, 03 October 2007 07:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Transaction id is defined with columns XIDUSN, XIDSLT, XIDSQN

Regards
Michel
Re: Extracting undo/redo via logminer for one specific transaction? [message #272129 is a reply to message #271932] Thu, 04 October 2007 01:53 Go to previous messageGo to next message
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
Member
Michel Cadot wrote on Wed, 03 October 2007 14:01
Transaction id is defined with columns XIDUSN, XIDSLT, XIDSQN


Thanks. Not sure if I asked the right question here.

I have a logminer package that starts logminer and extracts undo- and redo data. In this package I need to identify which transaction the package procedures are running in, as the package will be used in testing and as part of another script. At the end of the test, the logminer-package is to search out all operations done in this particular test script.

So the process goes something like this:
1. start test and record startup info such as time and some kind of transaction id
2. run tests, calling procedures that does insert/update/delete
3. test is done, so start up the logminer and find all operations. store the operations somewhere
4. undo the operations

So the difficult parts here are pt 1 and 3. Can I find the current transaction id at the start of this test and find the same id in v$logmnr_contents afterwards?

Regards,
Rolf
Re: Extracting undo/redo via logminer for one specific transaction? [message #272134 is a reply to message #272129] Thu, 04 October 2007 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> desc v$logmnr_contents
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 SCN                                       NUMBER
 CSCN                                      NUMBER
 TIMESTAMP                                 DATE
 COMMIT_TIMESTAMP                          DATE
 THREAD#                                   NUMBER
 LOG_ID                                    NUMBER
 XIDUSN                                    NUMBER
 XIDSLT                                    NUMBER
 XIDSQN                                    NUMBER
...
SQL>  desc v$transaction
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 ADDR                                      RAW(4)
 XIDUSN                                    NUMBER
 XIDSLOT                                   NUMBER
 XIDSQN                                    NUMBER
...
SQL> desc v$session
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 SADDR                                     RAW(4)
 SID                                       NUMBER
 SERIAL#                                   NUMBER
 AUDSID                                    NUMBER
 PADDR                                     RAW(4)
 USER#                                     NUMBER
 USERNAME                                  VARCHAR2(30)
 COMMAND                                   NUMBER
 OWNERID                                   NUMBER
 TADDR                                     VARCHAR2(8 CHAR)
...

v$session.taddr=v$transaction.addr
v$logmnr_contents.X%=v$transaction.X%

I think this is all what you need.

Regards
Michel
Re: Extracting undo/redo via logminer for one specific transaction? [message #272183 is a reply to message #272134] Thu, 04 October 2007 03:29 Go to previous message
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
Member
Michel Cadot wrote on Thu, 04 October 2007 08:59
I think this is all what you need.

I think you're right.

Thanks again, Michel.

Regards,
Rolf
Previous Topic: using dyanamic performance tables in functions and procs
Next Topic: Problems with Timestamp
Goto Forum:
  


Current Time: Fri Dec 09 03:46:21 CST 2016

Total time taken to generate the page: 0.05805 seconds