Materialized View TEST MANUAL

In the materialized view test manual, the view is an SQL statement that requires two-way data synchronization when data changes are small and data needs to be used twice, the execution plan must be re-generated and re-executed for each query.

In the materialized view test manual, the view is an SQL statement that requires two-way data synchronization when data changes are small and data needs to be used twice, the execution plan must be re-generated and re-executed for each query.

(

######################################## ######################################## ############

Select * fromALL_MVIEWS;

Select * fromUSER_MVIEW_ANALYSIS;

1.

Insertintosino_person_addressvalues (seq_sino_person_address.nextval, 123, to_date ('2017-04-2013: 12: 12', 'yyyy-mm-ddhh24: mi: ss'), '123 ', 'test _ report', 111, 'Beijing

Xicheng ', '000000', '1', 100100, '000000', 0 );

Insertintosino_person_addressvalues (seq_sino_person_address.nextval, 123, to_date ('2017-04-2013: 12: 12', 'yyyy-mm-ddhh24: mi: ss'), '123 ', 'test _ report2', 121, 'Beijing

Xicheng ', '000000', '2', 100200, '000000', 2 );

Insertintosino_person_addressvalues (values, 123, to_date ('2017-04-1012:12:12 ', 'yyyy-mm-ddhh24: mi: ss'), '2017', 'test _ report3', 2013, 'Beijing

Xicheng ', '000000', '3', 100300, '000000', 3 );

Commit

######################################## ######################################## ###################

2.

Selectowner, table_name, tablespace_name, statusfromdba_tableswheretable_namein ('sino _ LOAN_APPLY ');

UpdateSINO_LOAN_APPLYsetsorgcode = '000000' whereiid = 1000;

Execdbms_mview.refresh ('mv _ sino_loan_compact ', 'C ');

Execdbms_mview.refresh ('mv _ sino_loan_compact ', 'F ');

Execdbms_mview.refresh ('mv _ sino_loan_apply ', 'C ');

Execdbms_mview.refresh ('mv _ sino_loan_apply ', 'F ');

Execdbms_mview.refresh ('mv _ sino_loan_spec_trade ', 'C ');

Execdbms_mview.refresh ('mv _ sino_loan_spec_trade ', 'F ');

Execdbms_mview.refresh ('mv _ sino_loan ', 'C ');

Execdbms_mview.refresh ('mv _ sino_loan ', 'F ');

Execdbms_mview.refresh ('mv _ sino_loan_guarantee ', 'C ');

Execdbms_mview.refresh ('mv _ sino_loan_guarantee ', 'F ');

Execdbms_mview.refresh ('mv _ sino_loan_investor ', 'C ');

Execdbms_mview.refresh ('mv _ sino_loan_investor ', 'F ');

######################################## #######################################

Execdbms_mview.refresh ('mv _ sino_person_employment ', 'C ');

Execdbms_mview.refresh ('mv _ sino_person_employment ', 'F ');

Execdbms_mview.refresh ('mv _ sino_person_address ', 'C ');

Execdbms_mview.refresh ('mv _ sino_person_address ', 'F ');

Execdbms_mview.refresh ('mv _ sino_person_cerication ication ', 'C ');

Execdbms_mview.refresh ('mv _ sino_person_cerication ication ', 'F ');

Execdbms_mview.refresh ('mv _ sino_person ', 'C ');

Execdbms_mview.refresh ('mv _ sino_person ', 'F ');

3. ipbcstatenumber (1)

Sino_person_certification

Sino_person

Sino_person_address

Sino_person_employment

Sino_person_address_his

Sino_person_employment_his

Sino_person_his

Sino_loan

Sino_loan_compact

Sino_loan_spec_trade

Sino_loan_guarantee

Sino_loan_investor

Sino_loan_apply

######################################## ########################################

######################################## ######################################## ##

4.

Dbms_output.put_line ('jobnumberis '| to_char (job_num ));

Commit;

End;

/

Dbms_output.put_line ('jobnumberis '| to_char (job_num ));

Commit;

End;

/

Unique Job ID

Log_user: the user who submits the job

What is the stored procedure of job execution?

Last_date the date on which the last job was successfully run

Last_sec time when the last job was successfully run

Next_date: Date of the next running job

Next_sec next running job time

Failures: number of failed jobs. When an error occurs during job execution, Oracle records it in the log. The number of failed jobs is automatically increased by 1 each time. After the value is 16, Oracle does not execute it.

Whether the Broken is an abnormal job. When the number of failed jobs reaches 16, Oracle marks the job as a broken. Since then, Oracle will not continue to execute it until dbms_job.broken is re-set to notbroken, or dbms_job.run is forcibly called to re-execute it. Y indicates that the job is interrupted and will not run in the future. N indicates that the job is normal and can run.

Run a job
Begin
Dbms_job.run (: job_num); job_num is the variable that stores the job number.
End;

Query job status

SQL> selectjob, log_user, what, last_date, last_sec, next_date, next_sec, failures, brokenfromuser_jobs;

JOBLOG_USERWHATLAST_DATELAST_SECNEXT_DATENEXT_SECFAILURESBROKEN

Bytes -------------------------------------------------------------------------------------------------------------

1SINOJFSpro_refresh_all_mviews; 2013-4-26111: 27: 382013-4-27110: 00: 000N

Unique Job ID

Log_user: the user who submits the job

What is the stored procedure of job execution?

Last_date the date on which the last job was successfully run

Last_sec time when the last job was successfully run

Next_date: Date of the next running job

Next_sec next running job time

Failures: number of failed jobs. When an error occurs during job execution, Oracle records it in the log. The number of failed jobs is automatically increased by 1 each time. After the value is 16, Oracle does not execute it.

Whether the Broken is an abnormal job. When the number of failed jobs reaches 16, Oracle marks the job as a broken. Since then, Oracle will not continue to execute it until dbms_job.broken is re-set to notbroken during the User call process;

Or forcibly call dbms_job.run to execute it again. Y indicates that the job is interrupted and will not run in the future. N indicates that the job is normal and can run.

Delete a job

Begin
Dbms_job.remove (: job_num );
End;

Modify a job

Dbms_job.remove (jobno); Delete the job number

Example: executedbms_job.remove (1 );

######################################## ##############################

Dbms_job.what (jobno, what); Modify the Stored Procedure

Dbms_job.next_date (job, next_date) modifies the next execution time

Example: execdbms_job.next_date (46, sysdate + 2/(24*60); 46 job no.

######################################## #############################

Dbms_job.interval (job, interval): interval of Modification

Example: execdbms_job.interval (46, sysdate + 3/(24*60 ));

######################################## ##############################
Dbms_job.broken (job, true) interrupt job

Example: execdbms_job.broken (46, true); 46 job number: execdbms_job.broken (2, true) BROKEN = Y

######################################## ###############################

Dbms_job.broken (job, false, next_date) next_date: The next execution time. If not specified, start the job immediately.

Example: execdbms_job.broken (46, false); Start jobexecdbms_job.broken (2, false); BROKEN = N

######################################## ################################

Dbms_job.run (jobno); run the job

Example: executedbms_job.run (1 );

Please click to download

Leonarding

2013.08.29

Beijing & autumn

Sharing Technology ~ Achieving dreams

Blog:

This article is from the "leonarding Blog" Blog. Please keep this source