search
HomeDatabaseMysql TutorialOracle数据库快照的使用

Oracle数据库快照的使用

正在看的ORACLE教程是:Oracle数据库快照的使用。oracle数据库的快照是一个表,它包含有对一个本地或远程数据库上一个或多个表或视图的查询的结果。正因为快照是一个主表的查询子集,使用快照可以加快数据的查询速度;在保持不同数据库中的两个表的同步中,利用快照刷新,数据的更新性能也会有很大的改善。

  下面以我在开发襄樊市电信局170话费催缴系统中使用快照加快查询速度的实现过程为例来说明快照的使用方法:

  170话费催缴系统是一个向用户电话播放催缴话费提示音的系统。用户的欠费金额存放在rs6000小型机sffw用户下的表yh_qfcx中(yh_qfcx表是一个随用户缴费情况动态变化的欠费记录表),而催缴系统的数据按要求存放在另外一台xf170服务器dmtcx用户下,为在dmtcx用户下使用sffw用户下表yh_qfcx中的部分数据,我在dmtcx用户下建立了yh_qfcx的快照S_yh_qfcx,以加快查询速度。

  具体步骤如下:

  一、在sffw用户下建立表yh_qfcx的快照日志;

  只有先建立表yh_qfcx的快照日志,才能在快照中执行快速刷新。

  Create snapshot log on yh_qfcx;

  二、在dmtcx用户下建立到sffw用户的数据库链link_sf;

  建立了到sffw用户的数据库链后才能从sffw用户下的表yh_qfcx中获取数据。

  Create database link link_sf

  Connect to sffw identified by xxxxxxx using 'rs6000';

  三、在dmtcx用户下建立快照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;

  四、根据需要修改快照刷新的间隔时间;

  dmtcx用户下的快照s_yh_qfcx为了与sffw用户下的主表yh_qfcx保持同步,需要不断刷新快照。只有设定了快照的刷新间隔时间,oracle才会自动刷新该快照。

  快照的刷新有两种方式:快速刷新和完全刷新。快速刷新需要快照的主表先有快照日志存在;完全刷新时oracle执行快照查询,将结果放入快照。快速刷新比完全刷新快,因为快速刷新将主数据库的数据经网络发送到快照的数据少,仅需传送主表中修改过的数据,而完全刷新要传送快照查询的全部结果。

  Alter snapshot s_yh_qfcx refresh fast

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

  {此SQL语句的意思为:设定oracle自动在1分钟

  (1/24*60)后进行第一次快速刷新,以后每隔10分钟

  (10/24*60)快速刷新一次。}

  Alter snapshot s_yh_qfcx refresh complete

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

  {此SQL语句的意思为:设定oracle自动在30钞

  (30/24*60*60)后进行第一次完全刷新,

  以后每隔1天完全刷新一次。}

  说明:

  1、因为快照刷新是服务器自动完成的,所以要保证oracle数据库启动了快照刷新进程。查看oracle数据库是否启动了快照刷新进程,可以以数据库sys身份查看视图V_$SYSTEM_PARAMETER中的参数snapshot_refresh_processes的值是否为1,如果不为1,则快照刷新进程未启动。

  2、启动快照刷新进程的方法为:修改oracle数据库的初始化文件initorcl.ora,将其中的snapshot_refresh_processes参数的值改由0改为1,然后重新启动oracle数据即可。

  3、需要说明的是:建立快照日志时oracle数据库为我们建立了一个基于yh_qfcx的触发器tlog$_yh_qfcx和快照日志表mlog$_yh_qfcx;建立快照时oracle数据库为我们建立了一个表、两个视图、一个索引,它们分别为:

  一个表:snap$_s_yh_qfcx;

  两个视图:mview$_s_yh_qfcx和s_yh_qfcx;

  一个索引:I_snap$_s_yh_qfcx(

  基于表snap$_s_yh_qfcx中的m_row$$字段。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Reduce the use of MySQL memory in DockerReduce the use of MySQL memory in DockerMar 04, 2025 pm 03:52 PM

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

How to solve the problem of mysql cannot open shared libraryHow to solve the problem of mysql cannot open shared libraryMar 04, 2025 pm 04:01 PM

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

How do you alter a table in MySQL using the ALTER TABLE statement?How do you alter a table in MySQL using the ALTER TABLE statement?Mar 19, 2025 pm 03:51 PM

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

Run MySQl in Linux (with/without podman container with phpmyadmin)Run MySQl in Linux (with/without podman container with phpmyadmin)Mar 04, 2025 pm 03:54 PM

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

What is SQLite? Comprehensive overviewWhat is SQLite? Comprehensive overviewMar 04, 2025 pm 03:55 PM

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

Running multiple MySQL versions on MacOS: A step-by-step guideRunning multiple MySQL versions on MacOS: A step-by-step guideMar 04, 2025 pm 03:49 PM

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

How do I configure SSL/TLS encryption for MySQL connections?How do I configure SSL/TLS encryption for MySQL connections?Mar 18, 2025 pm 12:01 PM

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?Mar 21, 2025 pm 06:28 PM

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),