5 Replies Latest reply: Mar 20, 2013 9:29 AM by Chrisjenkins-Oracle RSS

    How to log delete queries

    paata_lom
      hello team,

      How I can log any delete query into log.

      I want audit but for only delete queries.



      Regards,
      Paata Lominadze.
        • 1. Re: How to log delete queries
          Tim Vincent
          Take a look at the Transaction Log API (XLA) i believe this will do what you want.
          http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21637/xla_event.htm#TTCDV246

          In particular take a look at the demo
          http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21637/xla_event.htm#CIHEBAHG

          You would simply inspect the record header and only print out (log to file) the deletes.

          If you're using C/C++ then I would recommend using ttClasses
          http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21640/usage.htm#autoId25

          Or if you are in the Java world there is a JMS/XLA interface
          http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21648/toc.htm

          Tim
          • 2. Re: How to log delete queries
            paata_lom
            Tim,

            Thanks for your answer, it was very helpful. We have created a simple XLA application based on xlaSimple.c and for every xla generates SQL using ttXlaGenerateSQL() function. Unfortunately the result is not suitable for our needs. Ideally, we would like to retrieve the actual SQL statement which triggered the update. If it's not possible to get the actual SQL statement, maybe it's possible to retrieve a process id which triggered an update or any other information which can help us to track an application which caused an update.

            Below you can find some information about our table, original query and the result from ttXlaGenerateSQL()
            Command> describe SC.TBL;
            
            Table SC.TBL:
              Columns:
               *GROUPID                         TT_INTEGER NOT NULL
               *ITEM                            TT_VARCHAR (32) INLINE NOT NULL
               *STARTDATE                       TT_BIGINT NOT NULL
                ENDDATE                         TT_BIGINT NOT NULL
            
            1 table found.
            (primary key columns are indicated with *)
            
            ...
            
            Command> select * from SC.TBL where groupid = 100100;
            < 100100, 790500010, 1358487630745, 2524593600000 >
            1 row found.
            
            ...
            
            Command> delete from SC.TBL where groupid = 100100;
            ...
            query generated by ttXlaGenerateSQL()
            
            DELETE from "SC"."TBL" WHERE "GROUPID"=100100 and "ITEM"='790500010' and "STARTDATE"=1358487630745
            Thanks in advance for any support. It's very much appreciated.
            • 3. Re: How to log delete queries
              Chrisjenkins-Oracle
              Hi,

              XLA is not intended as an 'auditing' solution. it;s main purpose is to allow applications to 'mine' the TimesTen transaction logs and react to data changes or DDL. Tim suggested it may be useful as we do have some customers who have used it to implement rudimentary auditing.

              TimesTen does not currently have any audit capability. The information you are seeking (actual SQL statement, process id, user, timestamp etc.) is not currently logged anywhere and thus it is not possible to obtain any historical record of this info from within TimesTen.

              Sorry.

              Chris
              • 4. Re: How to log delete queries
                paata_lom
                Chris,

                Thanks, It's clear. Meanwhile I have found out that I can use connID to narrow down the search. BTW, sorry for an offtopic here, but can you point me to C API functions or SYS table name which can be used to get connection information by connID (how did they code ttStatus?:))

                Thanks for your time.
                • 5. Re: How to log delete queries
                  Chrisjenkins-Oracle
                  Check out the two builtin procedures ttDataStoreStatus and ttContext in the TimesTen Database Reference Guide. They might be helpful.

                  Chris