ALTER MATERIALIZED VIEW mview_name COMPILE;
exec DBMS_MVIEW.REFRESH('owner.mview_name','C');
SELECT * FROM DBA_MVIEW_REFRESH_TIMES WHERE 1=1 AND NAME ='HOLX_ACTIVE_INSTL_CONTRACTS_MV';
select * from DBA_SNAPSHOT_REFRESH_TIMES where NAME ='HOLX_ACTIVE_INSTL_CONTRACTS_MV';
SELECT * FROM DBA_MVIEWS WHERE MVIEW_NAME ='HOLX_ACTIVE_INSTL_CONTRACTS_MV';;
select * from All_MVIEWS where mview_NAME ='HOLX_ACTIVE_INSTL_CONTRACTS_MV';;
1)
DBMS_REFRESH.REFRESH ('holx.holx_oic_calculated_detail_mv');
ORA-23404: refresh group "HOLX"."HOLX_OIC_CALCULATED_DETAIL_MV" does not exist
DBMS_REFRESH.REFRESH is used for a refresh group containing a list of mviews.
Please refer "http://docs.oracle.com/cd/E11882_01/server.112/e10707/rarrefreshpac.htm#i94176" for details.
Now check from DBA_REFRESH if its a refresh group or a single mview.
a) select ROWNER,RNAME from DBA_REFRESH where ROWNER='HOLX' and RNAME='HOLX_OIC_CALCULATED_DETAIL_MV';
> If it returns a result, then check step (b).
b) select OWNER,NAME,REFGROUP from sys.rgroup$ where OWNER='HOLX' and NAME='HOLX_OIC_CALCULATED_DETAIL_MV';
If step (a) does Not return a result, then
c) select * from dba_mviews where owner='HOLX' and MVIEW_NAME='HOLX_OIC_CALCULATED_DETAIL_MV';
( Please send me the step (c) output in a xl format for better reading. )
It should be a single mview.Then it is recommended to use DBMS_MVIEW.REFRESH instead of DBMS_REFRESH.REFRESH procedure while refreshing a single mview.
2)
ORA-12004: REFRESH FAST cannot be used for materialized view "HOLX"."HOLX_OIC_CALCULATED_DETAIL_MV"
The result from previous step (c) would clarify this.
> Check for the "FAST_REFRESHABLE" column value for this mview.
> Also ensure you have a MLOG$ in place for this mview with below query.
A MLOG$ is necessary condition for performing FAST refresh on a mview.
COLUMN LOG_TABLE HEADING 'Mview|Log Table' FORMAT A20
COLUMN LOG_OWNER HEADING 'Mview|Log Owner' FORMAT A10
COLUMN MASTER HEADING 'Master' FORMAT A20
COLUMN MVIEW_ID HEADING 'Mview|ID' FORMAT 9999
COLUMN NAME HEADING 'Mview Name' FORMAT A20
SELECT L.LOG_TABLE, L.LOG_OWNER, B.MASTER, B.MVIEW_ID, R.NAME ,R.UPDATABLE,R.REFRESH_METHOD
FROM DBA_MVIEW_LOGS L,DBA_BASE_TABLE_MVIEWS B,DBA_REGISTERED_MVIEWS R
WHERE B.MVIEW_ID = R.MVIEW_ID
AND B.OWNER = L.LOG_OWNER
AND B.MASTER = L.MASTER
AND R.NAME='HOLX_OIC_CALCULATED_DETAIL_MV';
> Now if you do not have a MLOG$ table from above query, then please create one as below and retry the FAST refresh.
CREATE MATERIALIZED VIEW LOG ON owner_name.master_table_name; ---- Simpleset way to create one.
Refer: http://docs.oracle.com/cd/E11882_01/server.112/e10707/rarmanmv.htm#REPMA389
3)
DBMS_SNAPSHOT.REFRESH( 'HOLX.HOLX_OIC_CALCULATED_DETAIL_MV','C')
This command is similar to using DBMS_MVIEW.REFRESH.
However, the option 'C' here forces a manual complete refresh on the mview for which you do not need a MLOG$ table on the master.
Hence it is going through successfully.
see the below examples ..
create table TEST_TABLE_JK
( rnum1 number,
rname1 varchar2(100));
Insert into TEST_TABLE_JK values (1,'A');
Insert into TEST_TABLE_JK values (2,'B');
commit;
Create view TEST_TABLE_JK_V as select * from TEST_TABLE_JK;
Create Materialized view TEST_TABLE_JK_MV as select * from TEST_TABLE_JK_V;
select * from TEST_TABLE_JK; -- 2records
select * from TEST_TABLE_JK_V; -- 2 records
select * from TEST_TABLE_JK_MV; -- 2 records
-- you will get 2 records in 3 select statements
Insert into TEST_TABLE_JK values (3,'C');
commit;
select * from TEST_TABLE_JK; -- 3records
select * from TEST_TABLE_JK_V; -- 3 records
select * from TEST_TABLE_JK_MV; -- 2 records
--you will get only two record s from MView where as three from table and View
--Because Mview will act as a snapshot and it shows onlt the records that exists when time of creation or Last Refresh.
begin
DBMS_MVIEW.REFRESH ('apps.TEST_TABLE_JK_MV');
end;
/
-- we can refersh the MView using above statements
select * from TEST_TABLE_JK; -- 3records
select * from TEST_TABLE_JK_V; -- 3 records
select * from TEST_TABLE_JK_MV; -- 3 records
-- Now after Refershing we will 3 records in each
delete TEST_TABLE_JK where RNAME1='B';
commit;
select * from TEST_TABLE_JK; -- 2records
select * from TEST_TABLE_JK_V; -- 2 records
select * from TEST_TABLE_JK_MV; -- 3 records
--you will get 3 records from MView where as only two from table and View
--Because Mview will act as a snapshot and it shows only the records that exists when time of creation or Last Refresh.
begin
DBMS_MVIEW.REFRESH ('apps.TEST_TABLE_JK_MV');
end;
/
select * from TEST_TABLE_JK; -- 2records
select * from TEST_TABLE_JK_V; -- 2 records
select * from TEST_TABLE_JK_MV; -- 2 records
select object_NAME, Object_type, status from all_objects where object_name like 'TEST_TABLE_JK%';
-- Now try to recreate the View
Create or replace view TEST_TABLE_JK_V as (select * from TEST_TABLE_JK where rname1 <> 'XXX');
select object_NAME, Object_type, status from all_objects where object_name like 'TEST_TABLE_JK%';
--if we modify the view the dependent Mview is will get Invalid
select * from TEST_TABLE_JK; -- 2records
select * from TEST_TABLE_JK_V; -- 2 records
select * from TEST_TABLE_JK_MV; -- 2 records
--But the select query will return the data as there was an table create with same name of Mview and it will remain in valid status even if we modified the Mview
begin
DBMS_MVIEW.REFRESH ('apps.TEST_TABLE_JK_MV');
end;
/
-- The Mview will get come back to valid status after refershing it.
-- Try to Refrsh with other command
begin
DBMS_SNAPSHOT.REFRESH ('apps.TEST_TABLE_JK_MV', 'F');
end;
/
-- It will through the below error
--Error report:
--ORA-12004: REFRESH FAST cannot be used for materialized view "APPS"."TEST_TABLE_JK_MV"
-- Here the paramenter 'F' is for Fast refresh
select * from dba_mviews where owner='APPS' and MVIEW_NAME='TEST_TABLE_JK_MV';
-- The Fast refresh column in the above Query is Set to 'NO' and it is the reason for Refresh Failed to FAst Refresh.
-- In this Case we should do complete reeresh instead of Fast Refresh (see the below statements)
begin
DBMS_SNAPSHOT.REFRESH ('apps.TEST_TABLE_JK_MV', 'C');
end;
/
-- The Above Statement will Refersh the Complete MV and it is Similar to "DBMS_MVIEW.REFRESH ('apps.TEST_TABLE_JK_MV'); "
--Now let us try to Refresh with other command
begin
DBMS_REFRESH.REFRESH ('apps.TEST_TABLE_JK_MV');
end;
/
--It will show the Below eRROR
--Error report:
--ORA-23404: refresh group "APPS"."TEST_TABLE_JK_MV" does not exist
--the command "DBMS_REFRESH.REFRESH ('apps.TEST_TABLE_JK_MV'); " will refersh only refresh groups but not Standalone Mviews or Single Mviews Which or not linked to a Refresh Group.
--Refresh group is a Group of Mviews. this group can contain 1 or More Mviews and all the Mvies in one refresh group can be refershed using 'DBMS_REFRESH.REFRESH ('apps.TEST_TABLE_JK_MV'); '
-- we can Query the Referesh group details using below select statement it will return the record with the regersh group name 'TEST_TABLE_JK'
select ROWNER,RNAME from DBA_REFRESH where ROWNER='APPS' and RNAME='TEST_TABLE_JK_MV';
-- Now let us try to create a refersh group with Name TEST_TABLE_JK_MV ( Here we can create a refresh with same nname as Mview also we can create with our own name)
BEGIN
DBMS_REFRESH.MAKE (
name => 'TEST_TABLE_JK_MV',
list => 'TEST_TABLE_JK_MV',
next_date => NULL,
interval => NULL,
implicit_destroy => TRUE,
purge_option => 1,
parallelism => 0,
heap_size => 0);
END;
/
select ROWNER,RNAME from DBA_REFRESH where ROWNER='APPS' and RNAME='TEST_TABLE_JK_MV';
-- Also we can add MVIEW to an Existing Refersh group
-- Lets Create another Mview First
Create Materialized view TEST_TABLE_JK_MV1 as select * from TEST_TABLE_JK_V;
BEGIN
DBMS_REFRESH.ADD (
name => 'TEST_TABLE_JK_MV',
list => 'TEST_TABLE_JK_MV1',
lax => TRUE);
END;
/
---now This Statement will refersh the Group and Mviews in the group with out error
begin
DBMS_REFRESH.REFRESH ('apps.TEST_TABLE_JK_MV');
end;
/
No comments:
Post a Comment