Home » SQL & PL/SQL » SQL & PL/SQL » Transaction Identification
Transaction Identification [message #208415] Sun, 10 December 2006 14:43 Go to next message
Messages: 7
Registered: December 2006
Location: Maribor,Slovenia
Junior Member
Hello Guys,

By the way great forum, I'm looking forward to browse interesting
features and questions on Oracle environment for managing data.

Let me allow to start my first session on this forum with my question.

I am trying to conceptually build the independent solution
( read indepenedently of transaction issued by OLTP applications on
RDBMS server ) to track transactions through independent triggers
( built on basis of dictionary data of OLTP tables ).

But the main task is to group and identify with some sequence all DML clauses ( Insert, Update, Delete ) that are in each transactions from the first statment to the commit point.

For example:

Some Oracle Forms application for enetering document data is
providing following data model and schema structure:

- browse header table of document ( Table A )
- update header table A ( Update TabA Set where ... )
- insert of details in TabB ( Detail TabB of Table A ).

So I have in this transaction two statements:
Insert Into TabA,
Update TabB

What I need is to have trackking tables for TabA - TabATrack and
TabB - TabBTrack that are history trace of transactions on original tables ( in remotely database ).

This two tables ( TabATrack and TabBTrack ) are maintained by triggers on TabA and TabB.

What I am asking is for the idea how to group all DML clauses in
TabATrack and TabBTrack with identity column in both tables called TransactionId that would have same value for all DML in original schema transactions.

This sequence should be set independently of application and somehow generated with as few application interventions.

Has anybody has simila problem or have an Idea?

Any hint, reference or idea is mostly apreciated.
Kind Regards

Re: Transaction Identification [message #208865 is a reply to message #208415] Tue, 12 December 2006 06:39 Go to previous messageGo to next message
Messages: 7062
Registered: December 2001
Senior Member
So, are you looking for auditing options?

Re: Transaction Identification [message #209289 is a reply to message #208415] Thu, 14 December 2006 02:03 Go to previous message
Messages: 7
Registered: December 2006
Location: Maribor,Slovenia
Junior Member
Auditing options?

Hmmm yes the original need is from the source of auditing options.

Well in the mean time I found some ideas that I haven't tested yet with DBMS_TRANSACTION Oracle supplied package and one idea from askTom dialogs: with join of V$Session and v$transaction table...

I must prepare the function and tested.

But the manner is not only for auditing ( although I'll use it
for management of user resources and defining relationships between group of SQL DML clauses and system reources and data management ).

But there is another issue.

Mangement of history log and transaction log tables that are part of the stage area for ETL process.

Previous Topic: snychronized role
Next Topic: How to write Linefeed Symbol (L above F) via UTL_FILE on VMS?
Goto Forum:

Current Time: Wed Aug 23 10:32:52 CDT 2017

Total time taken to generate the page: 0.11734 seconds