This content has been marked as final. Show 20 replies
981145 wrote:why do you want to reinvent the wheel?
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.
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?
981145 wrote:You may heed to SB's advice or spend time and resources to prepare a history table.
i want to keep data indefinitely with all the columns and the data sholud readily accessible.
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:
Edited by: Purvesh K on Feb 4, 2013 12:21 PM
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
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??????
981145 wrote:just create history table through copy of table structure:
i have created a materialized view with the name test.
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:if the Job is started, it should copy all data of m_view to history table:
when i refreshing test table the old values should move to test_hist and test will be updated with new values.
and then call the dbms_mview.refresh :
insert into history_table select sysdate, a.* from m_view a
DBMS_MVIEW.REFRESH('M_VIEW', 'C'); --completely refresh
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.
981145 wrote: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.
will this differentiates the data over the months???
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.
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
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.
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; /