This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Feb 15, 2013 10:28 AM by 755501 RSS

Table changes by timestamp

755501 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    more info needed
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points