1 2 Previous Next 20 Replies Latest reply: Feb 5, 2013 3:02 AM by Bawer RSS

    how to maintain historical data??????

    984148
      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
          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
            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
              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
                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
                  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
                    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
                      Thank you purvesh, i will try with it
                      • 8. Re: how to maintain historical data??????
                        Bawer
                        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
                          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
                            will this differentiates the data over the months???
                            • 11. Re: how to maintain historical data??????
                              Purvesh K
                              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
                                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
                                  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
                                    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