MATVIEWS

first experience with materialized views

1. the refresh needs hell lot of UNDO space and produces tonns of archivelog
2. if mview was created before the MVIEW LOG, then you cannot perform FAST refresh. You need to perform COMPLETE refresh first. Otherwise you will get ORA-12034: materialized view log on "%s"."%s" younger than last refresh. Metalink Note:365157.1 discusses this problem.
3. to refresh it manually, use the DBMS_MVIEW package's refresh procedure
DBMS_MVIEW.REFRESH('[MVIEW NAME]','C'); --where C is for Complete
4. MVIEW must be registered with it's MASTER site and be listed in SYS.SLOG$ table.
5. It is much cheaper to drop and re-create the m.view than to perform a complete refresh!

How to monitor the progress of a materialized view refresh (MVIEW)
MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring

export/import considerations

some things to consider when performing export/import operations:

- turn off the archive logging for the time of IMPORT if possible
- JOBS are created in the schema of the user who is running the import
- user must have the IMP_FULL_DATABASE privilege to be able to import JOBS
**I'm not sure which privilege exactly is necessary here. But tests show that if there's no IMP_FULL_DATABASE role, then data pump simply ignores the JOB objects in the dump**
- when performing SCHEMA import, it might be reasonable to import USER, ROLE_GRANTS, SYSTEM_GRANTS objects at first step logged in as DBA and all the rest in the second step as the schema user himself (so that JOBS will be created in the correct schema)