Clearing The Cache From Application

Clearing The Cache From Application 

Refreshing Materialized view's

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;

/

  

Create a Trace for Concurrent_program

Check the "Enable Trace" check box for the concurrent program that you want to get the trace file. and Save the changes from Sysadmin Responsibility.

Run the program from the respective Responsibility and notedown the request Id.

you can find the path of trace file using below Query.

select fcr.request_id "Request ID"
--, fcr.oracle_process_id "Trace ID"
, p1.value||'/'||lower(p2.value)||'_ora_'||fcr.oracle_process_id||'.trc' "Trace File"
, to_char(fcr.actual_completion_date, 'dd-mon-yyyy hh24:mi:ss') "Completed"
, fcp.user_concurrent_program_name "Program"
, fe.execution_file_name|| fe.subroutine_name "Program File"
, decode(fcr.phase_code,'R','Running')||'-'||decode(fcr.status_code,'R','Normal') "Status"
, fcr.enable_trace "Trace Flag"
from fnd_concurrent_requests fcr
, v$parameter p1
, v$parameter p2
, fnd_concurrent_programs_vl fcp
, fnd_executables fe
where p1.name='user_dump_dest'
and p2.name='db_name'
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.application_id = fe.application_id
and fcp.executable_id=fe.executable_id
and ((fcr.request_id = &request_id
or fcr.actual_completion_date > trunc(sysdate)))
order by decode(fcr.request_id, &request_id, 1, 2), fcr.actual_completion_date desc;

--you will be prompted to enter the request_id;

Defining Request Groups

Responsibility: System Administrator

Navigation: System Administrator>Security>Responsibility>Request

Enter Group Name :SA_SYSADMIN GROUP

Application: Application Object Library

Code: Leave Blank

Description: Sadik Admin Sysadmin Group

Enter Requests as below

Save

Check the Request Group attached to the  Responsibility SA_System Auditor

No Request Group is attached

Try to Run a Request View>Requests>Submit a New Request>Single Request>Ok

Now Attach the Request Group SA_Sysadmin Group the above Responsibility

Save

Now Switch to the Responsibility SA_System Auditor and Run a Request            

View>Requests>Submit a New Request>Single Request>Ok

Click on the LOV

You will see all the requests flowing in from the requests which are part of the Request Group Attached