Materialized View and scheduled job update example

Source: Internet
Author: User

Materialized View and job timed update example -- create test table create table test_dm_rep_m_gw_user as select * from dm_rep_m_gw_use t where t. month_no <'000000'; -- check and confirm that the maximum billing period of the current test table is select max (MONTH_NO) FROM TEST_DM_REP_M_GW_USER; -- create materialized view log on test_dm_rep_m_gw_user; -- because the test table has no primary key, the following error is reported when creating a view log based on the master table: ora-12014: table 'test _ dm_rep_m_gw_user 'does not contain a primary key constraint -- create a primary key for the test table. After the primary key is created, create the view log select * from test_dm_rep_m_gw_user according to the preceding statement; alter table test_dm_rep_m_gw_user add constraints primary key (month_no, prov_id, city_no, kpi_code); -- create a materialized view (for incremental refresh options, if an analysis function exists in the subquery, materialized view does not work) create materialized view my_test_dm_rep_m_gw_user refresh fast start with sysdate next sysdate + 2/60 with primary keyas select * from test_dm_rep_m_gw_user; drop materialized view detail; -- confirm the data record select COUNT (*) from dm_rep_m_gw_use t where t in the materialized view and base table. month_no <'200'; SELECT count (*) from nation; SELECT count (*) from test_dm_rep_m_gw_user; -- add the data record insert into test_dm_rep_m_gw_user select * from dm_rep_m_gw_use t where t. month_no = '000000'; commit; -- after several times, verify whether the data in the materialized view is updated -- job test -- create a test table create table job_test_01 (id number (12), c_date date) -- create a sequence g_seqminvalue 1 maxvalue 999999 start with 22 increment by 1 cache 20; -- create or replace procedure prc_g_test isbegininsert into job_test_01 (sequence, sysdate); end prc_t_test; -- create jobdeclare job number; beginsys. dbms_job.submit (job, 'prc _ g_test; ', sysdate, 'sysdate + 100'); end; -- View job: 1/1440 select * from user_jobs; -- run jobbegindbms_job.run (4023 ); end; -- view the data in the test table select * from job_test_01;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.