Page 716
ALTER SESSION ADVISE COMMIT;
Commit force `transaction id'; Rollback force `transaction_id';
NOTE |
The single quotes are required.n |
Page 717
A snapshot is simply a copy of a master table (or a subset) replicated to other child sites at specified intervals. The read-only snapshot is a faster solution than the distributed query (a select statement referencing a remote table). In addition to removing the network overhead, the hits on the remote table are significantly reduced. The snapshot can be one of the best methods of providing information to other remote sites, if the information needed does not have to be in real-time. The snapshots are normally refreshed on a predetermined schedule. Each snapshot or set of snapshots can have its own update schedule. Snapshots can be replicated to different locations depending upon the requirements of each site. In addition to scheduled refreshes, the DBA can manually refresh a snapshot. This is done using the DBMS_REFRESH procedure. They syntax for such a refresh is
EXECUTE DBMS_REFRESH.REFRESH (`Snapshotname');
NOTE |
In this format, the refresh will use the default of FAST. If a COMPLETE is desired, then this must be added to the procedure call.n |
The purposes for providing snapshots are the same as those of a simple distributed system. The following are specific reasons to use snapshots rather than dynamic database links:
For example, Big Widget Company has determined that due to increased reliance on the United States widget specifications, the distributed databases no longer support the business. Snapshots of the widget specifications are established. For business reasons, BWC has decided that Italy and Germany do not need to know what the other country's specifications are, but the United States needs to know what the widget specifications are for the other two countries. All the widget designs are kept in separate tables, so you don't need to combine the tables. The owner of the tables is the same worldwide to reduce confusion.
Creating snapshots is as simple as creating a table. Actually, this is what the DBA is doingcreating a copy of a table. The difference is that the copy becomes an automated process rather than a manual one. Once the table is copied, it is simply updated on a regular schedule to reflect the changes. The layout of the snapshot should be based on the same principles used to create a table.
The syntax for creating a snapshot is as follows:
CREATE SNAPSHOT [schema.]SNAPSHOT [Physical attributes *] [TABLESPACE tablespace] [LOB_storage_clause] [CACHE|NOCACHE] [CLUSTER (cluster column{,})]
Page 718
[table partition clause] [parallel clause] [USING INDEX physical attributes clause [LOGGING|NOLOGGING]TABLESPACE tablespace] [FAST|COMPLETE|FORCE] [START WITH date] [NEXT date] [WITH PRIMARY KEY|ROWID] [USING DEFAULT {MASTER|LOCAL}] ROLLBACK SEGMENT] [LOCAL|MASTER ROLLBACK SEGMENT rollback_segment] FOR UPDATE AS select_command;
For example, the following creates a simple snapshot:
CREATE SNAPSHOT milan_specification REFRESH FORCE START WITH SYSDATE next SYSDATE + 7 ;
START WITH and next of the CREATE SNAPSHOT command is based on SYSDATE. A whole number represents a single date. In the example, the next refresh rate is exactly 7 days from the first refresh. If next is not used, the snapshot will never refresh. If the refresh is to occur on a segment of time, such as an hour or half-hour, it will be in fractions. For example, a refresh rate of an hour will be SYSDATE + 1/24. A refresh rate of a half hour would be 1/48, every 15 minutes would be 1/96, and so on. If the refresh should always occur on a Monday, the syntax is a bit more complicated, such as the following:
NEXT_DAY(TRUNC(SYSDATE), `MONDAY')+3/24
NEXT_DAY is specified instead of SYSDATE. The 3 of the 24 represents 3 a.m.
Using Index allows the creation of an index that can improve performance when selecting from the snapshot.
FORCE/FAST/COMPLETE of the CREATE SNAPSHOT command is the method of replication. Complete is the most time intensive. This is recreating the snapshot. This is done by basically performing an INSERT INTO AS SELECT *. Complete is what is used to create the initial snapshot. If a table is a significant size, this option should be avoided after the initial creation. If the table is very large, please refer to offline instantiation later in this chapter.
Fast is the default. It will use the log to compare the two snapshots and only add what is in the log.
NOTE |
In a version 7 database, if a fast refresh fails, the snapshot refresh becomes broken and cannot be repaired without manual intervention. In version 8, if the Fast refresh fails, then a complete refresh will be performed. Fast refresh will work only if the snapshot has a snapshot log.n |
Force will perform a fast refresh unless unable to do so (for example, a broken refresh). This option is removed in version 8.
The management of snapshots does not change significantly between versions 7 and 8. The biggest change is in the addition of options within the create clause. Most of these changes are not directly related to the snapshot process. Most are due to the addition of new object types, new create table commands, and new create index commands. The differences are