Is there any way to see what DBMS_MVIEW.REFRESH is actually doing? I want to investigate partition change tracking (PCT) and verify that it is indeed only updating modified partitions.
1 Answer
Enable event 10046 at level 4 (bind values output) and then manually fire off a refresh. When you analyze the trace file (preferable using something like Orasrp), it will show you all the fun things that happen in the refresh. make sure you that in your session.
alter session set events '10046 trace name context forever, level 4' /
exec dbms_mview.refresh('blah'); commit; exit;
Then analyze tracefile using orasrp (since it shows you the call tree very nicely) or tkprof the old workhorse. However, tkprof will not show you bind values used, orasrop OTOH will. The output won't be pseudo code, but based on sequence of sqls, you will have to infer what is happening in the execution phase. – https://dba.stackexchange.com/users/26044