Created
September 8, 2019 21:58
-
-
Save xtender/d73064cfb8b9dde2032fe7639bc328d0 to your computer and use it in GitHub Desktop.
update-noupdate
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
set feed on; | |
drop table xt_curr1 purge; | |
drop table xt_curr2 purge; | |
-- simple table: | |
create table xt_curr1 as select '1' a, '2' b from dual connect by level<=1000; | |
-- same table but with empty trigger: | |
create table xt_curr2 as select '2' a, '2' b from dual connect by level<=1000; | |
-- objectID and SCN: | |
col obj1 new_val obj1; | |
col obj2 new_val obj2; | |
col scn new_val scn; | |
select | |
(select o.OBJECT_ID from user_objects o where o.object_name='XT_CURR1') obj1 | |
,(select o.OBJECT_ID from user_objects o where o.object_name='XT_CURR2') obj2 | |
,d.CURRENT_SCN scn | |
from v$database d | |
/ | |
-- logfile1: | |
alter system switch logfile; | |
col member new_val logfile; | |
SELECT member | |
FROM v$logfile | |
WHERE | |
is_recovery_dest_file='NO' | |
and group#=(SELECT group# FROM v$log WHERE status = 'CURRENT') | |
and rownum=1; | |
-- update1: | |
set autot trace stat; | |
update xt_curr1 set b=a; | |
set autot off; | |
commit; | |
-- dump logfile1: | |
alter session set tracefile_identifier='log1_diff'; | |
ALTER SYSTEM DUMP LOGFILE '&logfile' SCN MIN &scn OBJNO &obj1; | |
-- logfile2: | |
alter system switch logfile; | |
col member new_val logfile; | |
SELECT member | |
FROM v$logfile | |
WHERE | |
is_recovery_dest_file='NO' | |
and group#=(SELECT group# FROM v$log WHERE status = 'CURRENT') | |
and rownum=1; | |
-- update2: | |
set autot trace stat; | |
update xt_curr2 set b=a; | |
set autot off; | |
commit; | |
-- dump logfile2: | |
alter session set tracefile_identifier='log1_same'; | |
ALTER SYSTEM DUMP LOGFILE '&logfile' OBJNO &obj2; | |
alter session set tracefile_identifier='off'; | |
disc; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment