Oracle Database Discussions

10.2.0.3.0 linux

2 tables , 1 million rows, and 25 million rows. Both copies of each other in different schemas so sizings same per row.

Some reporting going against these which have been draggin the system so moving some of the data off to a new environment with fast refresh mviews over links.

first table is 1 mill rows, create mview log, runs in just under 90 seconds. Mview creates on linked DB in 3 minutes.
second table , 25 mill rows, create mvew log statement, I killed it after 2 hours at the start of the week but tried again yesterday, left it running and still running 22 hours later.

Database has high transactions against this largish table so Im thinking fragments and chaining so looked at the waits and getting high sequential reads. Monster in fact for the session creating the log. Does this sound like a good case for re-org? anyone seen an mview log hanging like that and is there a way to monitor the progress of how far the log has gone in creation, I know 22 hours is waayyyyy too long but if its not got too far left, I'll leave it run as its not causing any performance overhead that I can see.

log create statement

CREATE MATERIALIZED VIEW LOG ON user1.large_table
WITH SEQUENCE, ROWID
(cola,colb,colc,cold)
INCLUDING NEW VALUES;