Usually, it will take around 1 hour to finish the 10g upgrade from 9i, but this time, it took me more than 4 hours, that was amazing!
During the upgrade, I found the the EVENT: ‘db file scattered read’ from v$session_wait, and Oracle was updating table ‘aud$’:
UPDATE aud$
SET ntimestamp# = SYS_EXTRACT_UTC (CAST(timestamp# AS TIMESTAMP WITH TIME ZONE))
WHERE ntimestamp# IS NULL
I enabled auditing on this 9i database, which generated 2GB size of table aud$, and you may know that Oracle 10g addes many new features for auditing(you may check the table definition of aud$ between 9i and 10g), so it will also update table aud$ during the 10g upgrade.
So, you can use CTAS to backup table aud$ and then truncate it directly before 10g upgrade.
