1 2 Previous Next 18 Replies Latest reply: Feb 15, 2013 12:28 PM by 755501 RSS

    Table changes by timestamp

    755501
      I have a table that has a TIMESTAMP column that stores the last time the record was changed. I have to create a dbms_scheduler job (repeats at on 15 min interval) that will give me a list of all the table changes that happened during the timeframe. So I created a job that calls a stored procedure and executes every 15 minutes:

      table:
      emp
      last_name varchar2,
      ...
      last_change_date timestamp


      job:
      dbms_scheduler.create_job
      (job_name => 'CHECK_TABLE_DATA_JOB',
      job_type => 'PLSQL_BLOCK',
      job_action =>
      'begin
      CHECK_TABLE_DATA;
      end;',
      repeat_interval => 'FREQ=MINUTELY; INTERVAL=15'
      enabled=>true,
      auto_drop=>false);

      I'm a little unsure of the best way to query the changes in my procedure. I need to make sure that I catch all changes only 1 time, so I need to be precise on querying the 'last_change_date' timestamp column with time when job runs. Any ideas?
        • 1. Re: Table changes by timestamp
          sb92075
          bobmagan wrote:
          I have a table that has a TIMESTAMP column that stores the last time the record was changed. I have to create a dbms_scheduler job (repeats at on 15 min interval) that will give me a list of all the table changes that happened during the timeframe. So I created a job that calls a stored procedure and executes every 15 minutes:

          table:
          emp
          last_name varchar2,
          ...
          last_change_date timestamp


          job:
          dbms_scheduler.create_job
          (job_name => 'CHECK_TABLE_DATA_JOB',
          job_type => 'PLSQL_BLOCK',
          job_action =>
          'begin
          CHECK_TABLE_DATA;
          end;',
          repeat_interval => 'FREQ=MINUTELY; INTERVAL=15'
          enabled=>true,
          auto_drop=>false);

          I'm a little unsure of the best way to query the changes in my procedure. I need to make sure that I catch all changes only 1 time, so I need to be precise on querying the 'last_change_date' timestamp column with time when job runs. Any ideas?
          SELECT * FROM LOG_TABLE WHERE DML_TIMESTAMP > SYSTIMESTAMP - 15/1440;
          • 2. Re: Table changes by timestamp
            Nicolas.Gasparotto
            1 day is 24 hours.
            1 hour is 4*15mins.
            So, to get the time 15mins earlier :
            SQL> select sysdate,sysdate-1/24/4 from dual;
            
            SYSDATE             SYSDATE-1/24/4
            ------------------- -------------------
            11/02/2013 16:37:45 11/02/2013 16:22:45
            Nicolas.
            • 3. Re: Table changes by timestamp
              755501
              Thanks for the info.
              I'm just a little worried about the job_execution time vs the table change time. Is there a chance that the job execution time (every 15 minutes) will miss a change that happened.

              I was playing around with something like: systimestamp - interval '15' minute
              • 4. Re: Table changes by timestamp
                JohnWatson
                Are you totally committed to this processing model? Because if not, I would suggest an alternative implentation.

                This sort of thing is what Advanced Queueing is for. When you do something to a row, don't update any timestamp column, write a message to a queue. Then define a queue subscriber who will dequeue the messages every few seconds and take appropriate action. AQ will give you all the guarantees you might want: messages delivered once only, no possibility of losing messages, loads more. It's easy to implement through the pl/sql APIs.
                --
                John Watson
                http://skillbuilders.com
                • 5. Re: Table changes by timestamp
                  755501
                  What about using the flashback versions feature?

                  select last_name
                  from emp versions between timestamp systimestamp - interval '15' minutes and systimestamp:

                  Is that more reliable than the Advanced Que?
                  • 6. Re: Table changes by timestamp
                    JohnWatson
                    bobmagan wrote:
                    What about using the flashback versions feature?

                    select last_name
                    from emp versions between timestamp systimestamp - interval '15' minutes and systimestamp:

                    Is that more reliable than the Advanced Que?
                    I'm not sure that a flashback query could be guaranteed to survive a shutdown/startup?
                    --
                    John Watson
                    http://skillbuilders.com
                    • 7. Re: Table changes by timestamp
                      rp0428
                      >
                      I have a table that has a TIMESTAMP column that stores the last time the record was changed. I have to create a dbms_scheduler job (repeats at on 15 min interval) that will give me a list of all the table changes that happened during the timeframe. So I created a job that calls a stored procedure and executes every 15 minutes:
                      . . .
                      I'm a little unsure of the best way to query the changes in my procedure. I need to make sure that I catch all changes only 1 time, so I need to be precise on querying the 'last_change_date' timestamp column with time when job runs. Any ideas?
                      >
                      Your process is fatally flawed from the start. You CANNOT use such a timestamp column to capture 'the last time the record was changed'. The other flaw, that you may not care about, is that your process would not capture DELETEs that occur.

                      The FATAL flaw is that any value you TRY to store does not really exist, as far as other sessions (like your DBMS_JOB procedure) are concerned, until the transaction that changed the value is COMMITTED.

                      Consider this sequence of events:

                      1. your table is empt - no records at all

                      2. time 7:00 transaction1 - you insert 10 records - your timestamp column value is 7:00

                      3. tme 7:10 you commit transaction1

                      4. time 7:12 transaction2 - you insert 10 records - your timestamp column value is 7:10

                      5. time 7:15 transaction3 - your DBMS_JOB (or any other process) begins. It selects data for the 7:00 to 7:15 time period. ONLY transaction1 records are selected; transaction2 records don't exist since they have not yet been committed

                      6. time 7:22 you commit transaction2

                      7. time 7:30 transcation 4 - your DBMS_JOB begins again. It selects data for the 7:15 to 7:30 time period. THERE IS NO DATA! The transaction2 data is now commited by the timestamp column value is 7:10 which is before the 7:15 time period being selected.

                      That transaction2 data willl NEVER be processed.

                      The simplest method I know of is to create a materialized view log on the table and let Oracle worry about capturing the rows that are modified. All you need to capture is the PRIMARY KEY or ROWID of the rows and you will also get the DELETES. Then use the primary key values to grab the rows from the table that changes.

                      Although I suppose you could use change data capture it is being slowly deprecated.

                      See my reply to this same question from last year (Sep 20, 2012 10:21 AM) in this thread.
                      https://kr.forums.oracle.com/forums/thread.jspa?messageID=10589439
                      • 8. Re: Table changes by timestamp
                        755501
                        Thanks for the info. I'm not seeing how the MV log approach would capture the new/old values of all the columns in my table. What if I use the internal record timestamp instead of the one I manually populate? I am only concerned about the committed transactions. When a transaction wasn't committed right away, the timestamp was not set so it wasn't brought back inthe query. But as soon as it was committed, it returned on the next pull. So if I schedule a DBMS job to run every 15 minutes with the query below that always goes back 15 minutes to check, its going to miss records?


                        SELECT last_name,first_name, versions_starttime,versions_operation
                        FROM emp versions BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '15' MINUTE AND SYSTIMESTAMP
                        WHERE versions_starttime is not null
                        ORDER BY id, versions_endtime asc;

                        Edited by: bobmagan on Feb 12, 2013 4:47 AM

                        Edited by: bobmagan on Feb 12, 2013 7:41 AM
                        • 9. Re: Table changes by timestamp
                          rp0428
                          >
                          Thanks for the info. I'm not seeing how the MV log approach would capture the new/old values of all the columns in my table. What if I use the internal record timestamp instead of the one I manually populate? I am only concerned about the committed transactions. When a transaction wasn't committed right away, the timestamp was not set so it wasn't brought back inthe query. But as soon as it was committed, it returned on the next pull. So if I schedule a DBMS job to run every 15 minutes with the query below that always goes back 15 minutes to check, its going to miss records?

                          SELECT last_name,first_name, versions_starttime,versions_operation
                          FROM emp versions BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '15' MINUTE AND SYSTIMESTAMP
                          WHERE versions_starttime is not null
                          ORDER BY id, versions_endtime asc;
                          >
                          That query isn't even valid.

                          YES - it is going to miss records as explained above. The 'versions_starttime' has to get assigned a value in an INSERT or UPDATE statement but that statement is part of a transaction that won't get committed until some later time, maybe never. When the statement does get committed that 'commit' time will be different than the 'versions_starttime' value.

                          The simle MV log approach would not capture the actual changes. It would just let you know what rows had changed. Your process would then go get those changed rows and do something with them.

                          But you can create an MV log that captures ALL column changes and use the INCLUDING NEW VALUES clause. Then you have all of the changes available; even if a column changed 10 times during the time period you would have ALL of those changes.

                          After all that is what Oracle uses to incrementally refresh a materialized view.

                          See CREATE MATERIALIZED VIEW LOG in the docs
                          http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6003.htm
                          • 10. Re: Table changes by timestamp
                            755501
                            Thanks. Sorry, should have used ename (see below). I will read up on the MV log INCLUDING NEW VALUES.

                            I understand your reason why the record would get skipped based on 'versions_startime' or 'versions_endtime', but isn't the
                            'FROM emp versions BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '15' MINUTE AND SYSTIMESTAMP' statement using the internal (committted) timestamp to bring back the records?


                            SELECT ename, versions_starttime,versions_operation
                            FROM emp versions BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '15' MINUTE AND SYSTIMESTAMP
                            ORDER BY ename, versions_endtime asc;
                            • 11. Re: Table changes by timestamp
                              rp0428
                              >
                              Thanks. Sorry, should have used ename (see below). I will read up on the MV log INCLUDING NEW VALUES.

                              I understand your reason why the record would get skipped based on 'versions_startime' or 'versions_endtime', but isn't the
                              'FROM emp versions BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '15' MINUTE AND SYSTIMESTAMP' statement using the internal (committted) timestamp to bring back the records?

                              SELECT ename, versions_starttime,versions_operation
                              FROM emp versions BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '15' MINUTE AND SYSTIMESTAMP
                              ORDER BY ename, versions_endtime asc;
                              >
                              Well now you risk confusing the heck out of anyone reading this thread since you have abandoned the original question and have moved on to a new one.,

                              The original question was how to use YOUR column in YOUR table to query data changes that occur to your table. That has been answered as far as I can tell.

                              Now you are asking about FLASHBACK QUERY options and that is a new question. Many people that might be able to help you may not read the thread because your thread title is about something else altogether.

                              I suggest that you open a new thread with a subject including FLASHBACK QUERY. Post a link to this thread so people have the context.

                              Yes - you can use FLASHBACK QUERY functionality to identify changes to your table.

                              See the Advanced App Dev Guide for details
                              http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm#i1019938

                              The 'Using Oracle Flashback Version Query' section discusses the topic you mentioned.
                              >
                              Use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A row version is created whenever a COMMIT statement is executed.

                              Specify Oracle Flashback Version Query using the VERSIONS BETWEEN clause of the SELECT statement. The syntax is:

                              VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}
                              where start and end are expressions representing the start and end, respectively, of the time interval to be queried. The time interval includes (start and end).

                              Oracle Flashback Version Query returns a table with a row for each version of the row that existed at any time during the specified time interval. Each row in the table includes pseudocolumns of metadata about the row version, described in Table 12-1. This information can reveal when and how a particular change (perhaps erroneous) occurred to your database.
                              >
                              There are several examples in that section.
                              • 12. Re: Table changes by timestamp
                                755501
                                ok. Thanks for all the info. It looks like MV logs is anothe roption to consider along with flashback
                                • 13. Re: Table changes by timestamp
                                  755501
                                  I have been playing around with the MV log and it seems to be working quite nicely. The 1 thing that I can't get to work is removing information from the MV log file after I have processed. I saw from your previous post that you said:

                                  "Then you can write a process that mines that MV log file for the changes you need. That same process will need to cleanse the log file of changes that have been processed. Using an MV log will be about the lowest impact method you can use and the mechanism to capture the changes is already built-it to Oracle."

                                  Can I manually remove records from th MV log based on a specfic date/time?

                                  Thanks.
                                  • 14. Re: Table changes by timestamp
                                    755501
                                    more info needed
                                    1 2 Previous Next