Oracle Tips and Tricks — David Fitzjarrell

July 30, 2008

Say "Cheese!"

Filed under: General — dfitzjarrell @ 13:56

Materialized views and their associated snapshot logs are interesting animals with respect to the exp utility. Attempting to export a snapshot log by itself only provides the snapshot log table definition, as does attempting to do so with a table-level export. But that’s not all you need with a materialized view/snapshot to enable a fast refresh scenario, you need the data IN that snapshot log as well. So, how do you go about successfully exporting that data?

There are two modes of a traditional, original exp which can, and will, export snapshot log data: full export mode and user-level export mode. As these two modes export every object in a schema (all schemas for a full-mode export) all dependent objects on a table will be exported, which includes snapshot logs and their data because to export the snapshot log data the master table must also be exported. Yes, a table-level export can copy the master table, but it doesn’t follow the dependency tree to copy other tables/objects (and, yes, indexes are exported with the table in a table-level export), thus the snapshot logs are, well, left behind. Think of it in this manner: with a table-level export any table can be exported, by itself, without having to export any dependent tables which may exist (tables upon which foreign key constraints are defined). As such snapshot logs are also not exported in table-level mode.

If you want/need snapshot logs exported, along with their data, then you must perform a full or user-level export. There is no other choice.

If the snapshot log is defined using ROWID it will be necessary to perform a complete refresh immediately after importing the schema, as the stored ROWID values will be useless. Once a complete refresh is run all subsequent fast refreshes should execute without error. This is not the case with a primary key-based snapshot log; even immediately after import fast refreshes are possible.

So, to avoid surprises later (meaning ‘mysteriously’ missing snapshot logs) when you have snapshots/materialized views perform nothing less than a user-level export. This ensures you get your snapshot log definitions and data. And keeps your materialized view/snapshot fast refresh jobs running smoothly.


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at

%d bloggers like this: