Oracle10g snapshot operation method-mysql tutorial

This article describes how to create, refresh, and modify an oracle10g snapshot. For more information, see

This article describes how to create, refresh, and modify an oracle 10g snapshot. For more information, see

I accidentally heard about the snapshot of the oracle Database. I was stunned when I first heard it. I didn't know what it was, and I spent some time studying it, the following is my summary on network learning.

Snapshots are mainly used in distributed databases. we have Database A and table A in database a. we need to use the data in table A in database B, at this time, we can create A snapshot of database A in database B, which can improve our efficiency.

I understand that snapshots are used to copy a table to table B at regular intervals)

Note:Tables created from snapshots are read-only.

How to create a snapshot:

1. create A snapshot log for table a in database.

You can perform a quick refresh in a snapshot only when you create a snapshot log for Table.

Create snapshot log on;

2. create A database link to database A under Database B.

A) data can be obtained from table A in database A only after a database chain is established to database a (user ).

Create database link link_test

Connect to database A username identified by database A password using 'database name (database )'

This figure shows how to use pl/SQL to create a link View.

3. create a snapshot of Table a under Database B

Create snapshot t_a --> The name of the table displayed for the snapshot of Table a in data B in the future

Refresh complete start with sysdate + 1/24*60*60 next sysdate + 1/24*60

As select * from a @ link_test

SYSDATE + 1/24*60*60: indicates that oracle is automatically refreshed in 1 second, and next sysdate + 1/24*60

Indicates automatic refresh once every 1 minute.

Note:REFRESH is the REFRESH method.
There are two refresh methods: COMPLETE (full refresh) and FAST (FAST refresh,
Start with indicates the execution START time.
Next is the Next execution time.
In the future, AS is the method for querying snapshots.

Refresh method:

There are two ways to refresh a snapshot: quick refresh and full refresh. A snapshot log exists in the master table that requires a snapshot for quick refresh. when a snapshot is completely refreshed, oracle executes a snapshot query and places the result to the snapshot. Quick Refresh is faster than full refresh, because quick refresh only needs to transmit the modified data in the master table because there is less data sent from the master database to the snapshot through the network, and completely refresh all the results of the snapshot query to be sent.

4. modify a snapshot

Alter snapshot t_a refresh fast
Start with sysdate + 1/2880 next sysdate + 1

Meaning of this SQL statement: set oracle to automatically refresh for the first time after 30 seconds (30/24*60*60), and then refresh every other day

5. view the last refresh date of the snapshot.
Select name, LAST_REFRESH
FROM ALL_SNAPSHOT_REFRESH_TIMES

6. manually refresh the snapshot and execute it on the command interface.:
EXEC DBMS_SNAPSHOT.REFRESH ('t_ A', 'C ');
The first parameter is the snapshot name to be refreshed.
The second parameter is the refresh method, F----FAST, C---COMPLETE

7. description:

A) when snapshot logs are created, the oracle database creates a-based trigger for us (I did not find it)

Tlog $ _ a and snapshot log table mlog $ _ a (this is already found );

My online friend said that some tables and views were created during snapshot creation, but I only found the tables, and others were not found. I hope you can help me.

If you have any questions, please point them out and make progress together.