This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Feb 5, 2013 1:02 AM by Bawer RSS

how to maintain historical data??????

984148 Newbie
Currently Being Moderated
Hiii,,,

I will be very happy if anyone solve my question??????

I am trying to maintain historical data, I have created a table using materialized view ,and when ever i refreshed the table with new values the old value should move to the history table and the current table should updated with new values. I am using oracle 10g. can anyone tell how to do this.


Thankz in advance.
  • 1. Re: how to maintain historical data??????
    sb92075 Guru
    Currently Being Moderated
    981145 wrote:
    Hiii,,,

    I will be very happy if anyone solve my question??????

    I am trying to maintain historical data, I have created a table using materialized view ,and when ever i refreshed the table with new values the old value should move to the history table and the current table should updated with new values. I am using oracle 10g. can anyone tell how to do this.


    Thankz in advance.
    why do you want to reinvent the wheel?

    http://www.oracle.com/pls/db112/search?remark=quick_search&word=change+data+capture&partno=
  • 2. Re: how to maintain historical data??????
    Vite DBA Pro
    Currently Being Moderated
    Hi,

    you need to be more specific about what you mean by maintaining historical data.

    do you mean you wish to audit all the changes made to data or do you wish to keep data indefinitely or for a set period, not allowing deletes, but aging the data. Does the aged data need to be readily accessable?

    Regards
    Andre
  • 3. Re: how to maintain historical data??????
    984148 Newbie
    Currently Being Moderated
    hi...

    i have created a materialized view with the name test. i will refresh that materialized view once in a month. when i refreshing test table the old values should move to test_hist and test will be updated with new values. we use test_hist for some analysis. i am very new to this. please help..
  • 4. Re: how to maintain historical data??????
    984148 Newbie
    Currently Being Moderated
    Hi andre,

    i want to keep data indefinitely with all the columns and the data sholud readily accessible.
  • 5. Re: how to maintain historical data??????
    Purvesh K Guru
    Currently Being Moderated
    981145 wrote:
    Hi andre,

    i want to keep data indefinitely with all the columns and the data sholud readily accessible.
    You may heed to SB's advice or spend time and resources to prepare a history table.

    To manually prepare and maintain History data,
    1. Create a history Table, with your desired table structure
    2. Create a Trigger on your Base table, that will write all the changes to your History Table.

    One small demo to give you a start, in case you are not aware:
    drop table test_table;
    drop table test_table_hist;
    
    create table test_Table (pk_col number primary key, col1 number, col2 varchar2(5));
    
    create table test_table_hist (pk_col number, old_col1 number, new_col1 number, old_col2 varchar2(5), new_col2 varchar2(5), operation varchar2(10), mod_time timestamp);
    
    
    create or replace trigger trg_test_table_history
    before insert or delete or update of col1, col2
    on test_table
    for each row
    begin
      if inserting then
        insert into test_table_hist (pk_col, new_col1, new_col2, operation, mod_time)
        values (:new.pk_col, :new.col1, :new.col2, 'Insert', systimestamp);
      elsif updating then
        insert into test_table_hist (pk_col, old_col1, new_col1, old_col2, new_col2, operation, mod_time)
        values (:new.pk_col, :old.col1, :new.col1, :old.col2, :new.col2, 'Update', systimestamp);
      else
        insert into test_table_hist (pk_col, old_col1, old_col2, operation, mod_time)
        values (:old.pk_col, :old.col1, :old.col2, 'Delete', systimestamp);
      end if;
    end;
    
    
    
    insert into test_table values (1, 1001, 'ABCD');
    update test_table set col2 = 'ABCDZ' where pk_col = 1;
    insert into test_table values (2, 1002, 'PQRS');
    delete from test_table where pk_col = 2;
    
    commit;
    
    select *
      from test_table_hist;
    
    PK_COL                 OLD_COL1               NEW_COL1               OLD_COL2 NEW_COL2 OPERATION  MOD_TIME                  
    ---------------------- ---------------------- ---------------------- -------- -------- ---------- ------------------------- 
    1                                             1001                            ABCD     Insert     04-FEB-13 06.50.58.926695000 AM 
    1                      1001                   1001                   ABCD     ABCDZ    Update     04-FEB-13 06.50.59.099346000 AM 
    2                                             1002                            PQRS     Insert     04-FEB-13 06.50.59.264155000 AM 
    2                      1002                                          PQRS              Delete     04-FEB-13 06.50.59.427643000 AM
    Edited by: Purvesh K on Feb 4, 2013 12:21 PM
    Added Demo
  • 6. Re: how to maintain historical data??????
    984148 Newbie
    Currently Being Moderated
    hi purvesh,

    thankz for your response.. can you please share the logic to create trigger. it will help me alot...


    To be more specific, i have conncted to remote db through dblink and wrote materialized view on local db to get data from remote table. now the challenge is
    the values in the remote table is always changing. so i want to track the changes by maintaining the history table with all the columns. the materialized view i have created is test and i have created the history table as test_hist same as test table structure. now how to send the old values to the test_hist when the test is refreshed with updated values??????
  • 7. Re: how to maintain historical data??????
    984148 Newbie
    Currently Being Moderated
    Thank you purvesh, i will try with it
  • 8. Re: how to maintain historical data??????
    Bawer Journeyer
    Currently Being Moderated
    981145 wrote:
    i have created a materialized view with the name test.
    just create history table through copy of table structure:
    create history_table as
    select sysdate as date_backup, a.* from m_view a where 1=2
    981145 wrote:i will refresh that materialized view once in a month.
    define a Job with DBMS_SCHEDULER
    981145 wrote:
    when i refreshing test table the old values should move to test_hist and test will be updated with new values.
    if the Job is started, it should copy all data of m_view to history table:
    insert into history_table
    select sysdate, a.* from m_view a
    and then call the dbms_mview.refresh :
    DBMS_MVIEW.REFRESH('M_VIEW', 'C'); --completely refresh
  • 9. Re: how to maintain historical data??????
    Vite DBA Pro
    Currently Being Moderated
    Hi,

    If you want to keep the data indefinitely and available, just keep them in the same table. If you need to distinguish historical rows then just date stamp the historical records once they go into that category. If you are planning on storing lots of data, and I'm talking about many millions of rows, then you can partition the table. If the table is indexed and partitioned correctly then performance will not be a problem and you will not need to worry about maintaining history tables, MV's, inserting and deleting data, doing unionised queries and creating processes to make data historical, except maybe for updating rows. If at some stage in the future you decide you want to archive data, then partitioning will make this relatively simple.

    Remember the KISS principle.

    Regards
    Andre
  • 10. Re: how to maintain historical data??????
    984148 Newbie
    Currently Being Moderated
    will this differentiates the data over the months???
  • 11. Re: how to maintain historical data??????
    Purvesh K Guru
    Currently Being Moderated
    981145 wrote:
    will this differentiates the data over the months???
    With SYSDATE included in your History table, you can always filter the data for a Particular month, can't you?
  • 12. Re: how to maintain historical data??????
    Bawer Journeyer
    Currently Being Moderated
    981145 wrote:
    will this differentiates the data over the months???
    the column date_backup is for this purpose. Surely you can use another tools like sequence but in your case (once in a Month) it is enough to keep the data with a date.
    or you can ad a new column in your materialized view named creation_date filled with sysdate and you can keep the structure of historical_table exactly same as materialized view.
  • 13. Re: how to maintain historical data??????
    984148 Newbie
    Currently Being Moderated
    hiii bawer,

    I have tried this and it is working fine but along with sysdate i need to upload acutal month of the particular data and also like this i need to update nearly 22 tables.

    can you please suggest me how to create procedure with..
    month - input by user
    and
    same code as suggested by you earlier...

    based on that i will implement same for all 22 history tables.

    Thank you very much for your help. very much appreciated.
  • 14. Re: how to maintain historical data??????
    Purvesh K Guru
    Currently Being Moderated
    Untested piece, but should give you a skeleton to proceed.
    create or replace procedure populate_history(p_month date)
    is
    
    begin
      insert into history_table
      select sysdate, tabs.*
        from m_view tabs
       where to_char(tabs.some_date_column, 'MONTH') = to_char(p_month, 'MONTH');   --some_date_column is a column which denotes the Data 
                                                                                    --for a particular month that you need to populate in History
    end populate_history;
    
    
    begin
    
      dbms_scheduler.create_job(
          job_name => 'History_Job'
         ,job_type => 'PLSQL_BLOCK'
         ,job_action => 'begin populate_history(sysdate) end;'
         ,start_date => trunc(sysdate)
           ,repeat_interval => 'FREQ=MONTHLY;'
         ,enabled => TRUE
         ,comments => 'test');
    
    end;
    /
1 2 Previous Next

Legend

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