Use of Oracle database snapshots _oracle

The Oracle tutorial you are looking at is the use of Oracle database snapshots. A snapshot of an Oracle database is a table that contains the results of a query on one or more tables or views of a local or remote database. Because a snapshot is a subset of a query for a primary table, using snapshots speeds up the query speed of the data; with snapshot refresh, the update performance of the data can be improved greatly in the synchronization of two tables in different databases.

Here's how I use snapshots to speed up the query in the development of the Xiangfan Telecommunication 170 calling system. For example, the use of a sprightly photo:

170 Call payment system is a system that plays reminders to the user on the phone. The amount owed by the user is stored in the table YH_QFCX of the rs6000 minicomputer SFFW user (the YH_QFCX table is a record table with the dynamic change of the user's payment situation), and the data of the collection system is stored in another xf170 server dmtcx the user under the request, To use some of the data from the SFFW user below YH_QFCX under the DMTCX user, I established YH_QFCX snapshot s_yh_qfcx under the DMTCX user to speed up the query.

　　The specific steps are as follows:

First, create the snapshot log of table YH_QFCX under the SFFW user;

You can perform a quick refresh in a snapshot only if you establish a snapshot log of the table YH_QFCX.

Create snapshot log on YH_QFCX;

Secondly, the database chain of the SFFW user is established under the DMTCX user link_sf;

The database chain to the SFFW user is established to obtain data from the table YH_QFCX under the SFFW user.

Create Database Link LINK_SF

Connect to SFFW identified by xxxxxxx using ' rs6000 ';

Third, under the DMTCX user to establish the snapshot s_yh_qfcx;

Create Snapshot s_yh_qfcx as

Select YHH,QF6+QF5+QF4+QF3+QF2+QF1+QF Qfje

From YH_QFCX@LINK_SF

Where tjbz= ' K ' and bz6+bz5+bz4+bz3+bz2+bz1+bz>0;

Revise the time interval of snapshot refresh as needed;

Snapshots under DMTCX users S_YH_QFCX in order to synchronize with the primary table YH_QFCX under the SFFW user, you need to constantly refresh the snapshot. Oracle will automatically refresh the snapshot only if the snapshot's refresh interval is set.

There are two ways to refresh a snapshot: Quick refresh and full refresh. A quick refresh requires the snapshot's primary table to exist first, and Oracle executes the snapshot query when full refresh, putting the results in the snapshot. Quick flushes are faster than full flushes because the quick refresh is less data that sends the primary database's data over the network to the snapshot, only the modified data in the primary table is routed, and the full results of the snapshot query are completely refreshed.

Alter Snapshot s_yh_qfcx Refresh Fast

Start with sysdate+1/1440 next sysdate+1/144;

{This SQL statement means: Set Oracle automatically in 1 minutes

(1/24*60) After the first quick refresh, every 10 minutes later

(10/24*60) a quick refresh. }

Alter Snapshot s_yh_qfcx Refresh complete

Start with sysdate+1/2880 next sysdate+1;

{This SQL statement means: Set Oracle automatically in 30 banknotes

(30/24*60*60) After the first full refresh,

Refresh every 1 days in the future. }

　　Description

1. Because snapshot refreshes are done automatically by the server, make sure that the Oracle database starts the snapshot refresh process. To see if the Oracle database has started the snapshot refresh process, you can view the parameters in the view v_$system_parameter as database sys snapshot_refresh_processes whether the value is 1, or if it is not 1, the snapshot refresh process does not start.

2. The way to start the snapshot refresh process is to modify the initialization file Initorcl.ora of the Oracle database, change the value of the snapshot_refresh_processes parameter from 0 to 1, and then restart the Oracle data.

3, need to note that: When the snapshot log is established, the Oracle database builds a YH_QFCX trigger tlog$_yh_qfcx and snapshot log table MLOG$_YH_QFCX; The Oracle database creates a table, two views, An index, which is:

a table: SNAP$_S_YH_QFCX;

Two views: Mview$_s_yh_qfcx and S_YH_QFCX;

An index: I_SNAP$_S_YH_QFCX (

Based on the m_row$$ field in table SNAP$_S_YH_QFCX.