Stay at Home!! Be Safe!! Take Care!!

For Any queries, please mail us at support@funoracleapps.com

How to Stop Auto Refresh of Materialized view in Oracle

No comments

How to Stop Auto Refresh of  Materialized view in Oracle


Disable Automatic Refresh of Materialized View.

STEP 1. 
Connect as mview owner and execute the below query.

select * from user_jobs where broken ='N';

STEP 2. 
In the WHAT column for the mview refresh job we will see:

dbms_refresh.refresh('"[OWNER]"."[MVIEW_NAME]"');
STEP 3. 
Get the JOB_ID form the job column and execute below procedure and mview will stop refreshing automatically:

 
begin
dbms_job.broken(JOB_ID,TRUE);
commit;
end;
/
Example:

begin
dbms_job.broken(30,TRUE);
commit;
end;
/


How to start refreshing Mview again just run the job.

begin
dbms_job.run(JOB_ID);
commit;
end;
/
Example:


 
begin
dbms_job.broken(30,FALSE);
commit;
end;
/

No comments :

Post a Comment