Materialized View and scheduled job update example

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;