alter database add supplemental log data (primary key) columns;
数据库默认情况下会记载update的rowid以及update的新值和旧值。
create table t1 (a int primary key, b int, c int, d int, e int);
create table t2 (a int, b int, c int, d int, e int);
insert into t1 values (1,1,1,1,1);
insert into t2 values (1,1,1,1,1);
insert into t1 values (2,2,1,1,1);
insert into t2 values (2,2,1,1,1);
commit;
Test 1:
select supplemental_log_data_pk from v$database;
NO
update t1 set c=10;
update t2 set c=10;
commit;
select sql_redo from v$Logmnr_contents where seg_name like 'T%' and sql_redo like 'update%
update "TEST"."T1" set "C" = '10' where "C" = '1' and ROWID = 'AAAHZjAABAAAMYyAAA';
update "TEST"."T1" set "C" = '10' where "C" = '1' and ROWID = 'AAAHZjAABAAAMYyAAB';
update "TEST"."T2" set "C" = '10' where "C" = '1' and ROWID = 'AAAHZlAABAAAMZCAAA';
update "TEST"."T2" set "C" = '10' where "C" = '1' and ROWID = 'AAAHZlAABAAAMZCAAB';
Test 2:
alter database add supplemental log data (primary key) columns;
select supplemental_log_data_pk from v$database;
YES
select sql_redo from v$Logmnr_contents where seg_name like 'T%' and sql_redo like 'update%
update "TEST"."T1" set "C" = '-10' where "A" = '1' and "C" = '10' and ROWID = 'AAAHZjAABAAAMYyAAA';
update "TEST"."T1" set "C" = '-10' where "A" = '2' and "C" = '10' and ROWID = 'AAAHZjAABAAAMYyAAB';
update "TEST"."T2" set "C" = '-10' where "A" = '1' and "B" = '1' and "C" = '10' and "D" = '1' and "E" = '1' and ROWID = 'AAAHZlAABAAAMZCAAA';
update "TEST"."T2" set "C" = '-10' where "A" = '2' and "B" = '2' and "C" = '10' and "D" = '1' and "E" = '1' and ROWID = 'AAAHZlAABAAAMZCAAB';
数据库默认情况下会记载update的rowid以及update的新值和旧值。
create table t1 (a int primary key, b int, c int, d int, e int);
create table t2 (a int, b int, c int, d int, e int);
insert into t1 values (1,1,1,1,1);
insert into t2 values (1,1,1,1,1);
insert into t1 values (2,2,1,1,1);
insert into t2 values (2,2,1,1,1);
commit;
Test 1:
select supplemental_log_data_pk from v$database;
NO
update t1 set c=10;
update t2 set c=10;
commit;
select sql_redo from v$Logmnr_contents where seg_name like 'T%' and sql_redo like 'update%
update "TEST"."T1" set "C" = '10' where "C" = '1' and ROWID = 'AAAHZjAABAAAMYyAAA';
update "TEST"."T1" set "C" = '10' where "C" = '1' and ROWID = 'AAAHZjAABAAAMYyAAB';
update "TEST"."T2" set "C" = '10' where "C" = '1' and ROWID = 'AAAHZlAABAAAMZCAAA';
update "TEST"."T2" set "C" = '10' where "C" = '1' and ROWID = 'AAAHZlAABAAAMZCAAB';
Test 2:
alter database add supplemental log data (primary key) columns;
select supplemental_log_data_pk from v$database;
YES
select sql_redo from v$Logmnr_contents where seg_name like 'T%' and sql_redo like 'update%
update "TEST"."T1" set "C" = '-10' where "A" = '1' and "C" = '10' and ROWID = 'AAAHZjAABAAAMYyAAA';
update "TEST"."T1" set "C" = '-10' where "A" = '2' and "C" = '10' and ROWID = 'AAAHZjAABAAAMYyAAB';
update "TEST"."T2" set "C" = '-10' where "A" = '1' and "B" = '1' and "C" = '10' and "D" = '1' and "E" = '1' and ROWID = 'AAAHZlAABAAAMZCAAA';
update "TEST"."T2" set "C" = '-10' where "A" = '2' and "B" = '2' and "C" = '10' and "D" = '1' and "E" = '1' and ROWID = 'AAAHZlAABAAAMZCAAB';
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30426/viewspace-201813/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30426/viewspace-201813/