OverviewAnother feature of Oracle that needs administration is the snapshot (also known as a materialized view.) Snapshots are copies of either an entire single table or set of its rows (simple snapshot) or a collection of tables, views, or their rows using joins, grouping, and selection criteria (complex snapshots). Snapshots are very useful in a distributed environment where remote locations need a queriable copy of a table from the master database. Instead of paying the penalty for using the network to send out the query and get back the data, the query is against a local table image and is thus much faster. With later versions of Oracle7 and in Oracle8 and Oracle8i, snapshots can be made updateable. Snapshots and materialized views are asynchronous in nature; they reflect a table's or a collection's state at the time the snapshot was taken. A simple snapshot or materialized view can be periodically refreshed by either use of a snapshot log containing only the changed rows for the snapshot (fast refresh), or a totally new copy (complete refresh). In most cases, the fast refresh is quicker and just as accurate. A fast refresh can only be used if the snapshot or materialized view has a log, and that log was created prior to the creation or last refresh of the snapshot. For a complex snapshot or materialized view, a complete refresh is required. It is also possible to allow the system to decide which to use, either a fast or complete refresh.One problem with a snapshot or materialized view log is that it keeps a copy of each and every change to a row. Therefore, if a row undergoes 200 changes between one refresh and the next, there will be 200 entries in the snapshot or materialized view log that will be applied to the snapshot at refresh. This could lead to the refresh of the snapshot or materialized view taking longer than a complete refresh. Each snapshot or materialized view should be examined for the amount of activity it is seeing and if this is occurring with any of them, the snapshot or materialized view log should be eliminated or the refresh mode changed to COMPLETE.A materialized view is simply a snapshot that is contained in the current instance instead of a remote instance. Other than the keyword MATERIALIZED VIEW the CREATE SNAPSHOT and CREATE SNAPSHOT LOG commands are identical to the CREATE MATERIALIZED VIEW and CREATE MATERIALIZED VIEW LOG commands. Since the CREATE MATERIALIZED VIEW command creates a view, table and an index to maintain the materialized view you must have the CREATE VIEW, CREATE TABLE, CREATE INDEX and CREATE MATERIALIZED VIEW or CREATE SNAPSHOT privileges to create a materialized view. If you wish query rewrite to be available on the materialized views created, the owner of the underlying tables and the materialized view must have QUERY REWRITE or, the creator of the materialized view must have GLOBAL QUERY REWRITE privilege. In a data warehousing situation a materialized view can be used by Oracle to re-write queries on the fly that the optimizer determines would profit from using the materialized view rather than the base tables. You should take this into consideration when the concurrency of the data is important since a materialized view is only as current as its last refresh.Snapshot Typesw Simple - consists of either an entire single table, or, a simple select of rows from a single table. w Complex - consists of joined tables, views, grouped or complex select statement queries. Restrictionsw Snapshots cannot be created on tables owned by the SYS user. w Data can only be selected from a snapshot; no updates, inserts or deletions are allowed. w To create a simple snapshot, the snapshot's defining query cannot contain the following SQL attributes: distinct or aggregate functions GROUP BY or CONNECT BY clauses joins (other than the allowed types of subqueries) set operations When a snapshot definition uses any of the above attributes, the snapshot is a complex snapshot. Oracle cannot use a snapshot log to perform fast refreshes for a complex snapshot. Special Requirements for Primary Key Snapshots (Oracle8)By default, Oracle creates a snapshot log to support primary key snapshots. Therefore, the master table must contain a valid PRIMARY KEY constraint before you can create a snapshot log. NamingA snapshot's name cannot exceed 19 characters in length. This is because Oracle adds its own suffixes to the table name used for the snapshot. An Oracle table's name cannot exceed 30 characters. As with other Oracle objects, it pays to make the snapshot name meaningful.IndexingWhen you create a snapshot, Oracle automatically creates a table in the replicated database. The table name has the prefix SNAP$_. You will often want to index the snapshot table differently to the table being replicated from. This is especially true if the main database is being used for different purposes, for example, the database being replicated from being used for OLTP and the database being replicated to being used for batch reporting. Oracle will also create its own index on the ROWID column if the snapshot is created using the rowid option and will be on the primary key columns if the snapshot is created with the primary key columns.Recommendationsw Use snapshots in a distributed environment where remote locations need a queriable copy of a table from the master database. Instead of paying the penalty for using the network to send out the query and get back the data, the query is against a local table image and is thus much faster. w Snapshot size should mirror those for the source table. If the source table is stable, a large initial extent with smaller subsequent extents should be used. Since snapshots will most likely be on slow growth tables, set PCTINCREASE to zero in most cases.Using DBMS_JOB to Supplement Snapshot ReplicationThe dbms_job facility can be thought of in much the same manner as the Unix utility cron: a simple scheduling tool. Exploiting the dbms_job functionality allows us to build a much more sophisticated form of data replication. Instead of being limited by Oracle's own snapshot replication restrictions, it is a very simple task to construct your own business data replication engine.
GROUP BY or CONNECT BY clauses
joins (other than the allowed types of subqueries)
set operations When a snapshot definition uses any of the above attributes, the snapshot is a complex snapshot. Oracle cannot use a snapshot log to perform fast refreshes for a complex snapshot. Special Requirements for Primary Key Snapshots (Oracle8)By default, Oracle creates a snapshot log to support primary key snapshots. Therefore, the master table must contain a valid PRIMARY KEY constraint before you can create a snapshot log. NamingA snapshot's name cannot exceed 19 characters in length. This is because Oracle adds its own suffixes to the table name used for the snapshot. An Oracle table's name cannot exceed 30 characters. As with other Oracle objects, it pays to make the snapshot name meaningful.IndexingWhen you create a snapshot, Oracle automatically creates a table in the replicated database. The table name has the prefix SNAP$_. You will often want to index the snapshot table differently to the table being replicated from. This is especially true if the main database is being used for different purposes, for example, the database being replicated from being used for OLTP and the database being replicated to being used for batch reporting. Oracle will also create its own index on the ROWID column if the snapshot is created using the rowid option and will be on the primary key columns if the snapshot is created with the primary key columns.Recommendationsw Use snapshots in a distributed environment where remote locations need a queriable copy of a table from the master database. Instead of paying the penalty for using the network to send out the query and get back the data, the query is against a local table image and is thus much faster. w Snapshot size should mirror those for the source table. If the source table is stable, a large initial extent with smaller subsequent extents should be used. Since snapshots will most likely be on slow growth tables, set PCTINCREASE to zero in most cases.Using DBMS_JOB to Supplement Snapshot ReplicationThe dbms_job facility can be thought of in much the same manner as the Unix utility cron: a simple scheduling tool. Exploiting the dbms_job functionality allows us to build a much more sophisticated form of data replication. Instead of being limited by Oracle's own snapshot replication restrictions, it is a very simple task to construct your own business data replication engine.
能否告知一二
基本正常。但我只是会用，一旦出现异常时我就束手无策，还想你能把
这些东西整理一下贴出来，为大家做点贡献是最快乐，不知道您是否这样
认为
http://expert.csdn.net/Expert/topic/1140/1140921.xml?temp=.2825128