materialized view execution

[Arsalan]

Salam bro.

I have 100 materialized view so how to manually refresh it .

scott/tiger

set serveroutput on

declare
cursor c_emp is select * from user_objects s where s.OBJECT_TYPE='MATERIALIZED VIEW';
r_emp c_emp%rowtype;
B VARCHAR2(13);
begin
open c_emp;
for i in 1..100 loop
fetch c_emp into r_emp;
exit when c_emp%notfound;
dbms_output.put_line('execute dbms_mview.refresh('||'list=>'||r_emp.object_name||','||'method=>'||'F'||')');
end loop;
close c_emp;
end;
/
---- this is the out put

execute dbms_mview.refresh(list=>BONUS,method=>F)

execute dbms_mview.refresh(list=>DEPT,method=>F)

-------but i want output line below to refresh the materialized view

execute dbms_mview.refresh( list => 'BONUS', method => 'F' );


please guide me other easy way how to execute or refresh 100 materialized view
in one command because i have 100 materialized view

[ErmanArslansOracleBlog]

Hi Arsala,

You can use "materialized view Refresh Groups" for that.

3 Ara 2016 07:52 tarihinde "Arsala saleem [via Erman Arslan's Oracle Forum]" <[hidden email]> yazdı:

Salam bro.

I have 100 materialized view so how to manually refresh it .

scott/tiger

set serveroutput on

declare
cursor c_emp is select * from user_objects s where s.OBJECT_TYPE='MATERIALIZED VIEW';
r_emp c_emp%rowtype;
B VARCHAR2(13);
begin
open c_emp;
for i in 1..100 loop
fetch c_emp into r_emp;
exit when c_emp%notfound;
dbms_output.put_line('execute dbms_mview.refresh('||'list=>'||r_emp.object_name||','||'method=>'||'F'||')');
end loop;
close c_emp;
end;
/
---- this is the out put

execute dbms_mview.refresh(list=>BONUS,method=>F)

execute dbms_mview.refresh(list=>DEPT,method=>F)

-------but i want output line below to refresh the materialized view

execute dbms_mview.refresh( list => 'BONUS', method => 'F' );


please guide me other easy way how to execute or refresh 100 materialized view
in one command because i have 100 materialized view

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/materialized-view-execution-tp1648.html

To start a new topic under Database, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

[Arsalan]

Sir

You just edit on script  i want to out put like this


------- i want output like below to refresh the materialized view

execute dbms_mview.refresh( list => 'BONUS', method => 'F' );


--but it give output like below please, so i can't concatenate tablename like  'BONUS'

execute dbms_mview.refresh(list=>BONUS,method=>F)

execute dbms_mview.refresh(list=>DEPT,method=>F)


scott/tiger

set serveroutput on

declare
cursor c_emp is select * from user_objects s where s.OBJECT_TYPE='MATERIALIZED VIEW';
r_emp c_emp%rowtype;
B VARCHAR2(13);
begin
open c_emp;
for i in 1..100 loop
fetch c_emp into r_emp;
exit when c_emp%notfound;
dbms_output.put_line('execute dbms_mview.refresh('||'list=>'||r_emp.object_name||','||'method=>'||'F'||')');
end loop;
close c_emp;
end;
/

[ErmanArslansOracleBlog]

Arsala,

You need to create a Materialized view refresh group.

Note: All materialized views that are added to a particular refresh group are refreshed at the same time. This ensures transactional consistency between the related materialized views in the refresh group.

Read :https://docs.oracle.com/cd/B28359_01/server.111/b28327/rarmviewgroup.htm#i18374