No question at this time
DBA Top 10
1 A. Kavsek 8400
2 B. Vroman 5900
3 M. Cadot 5400
4 P. Wisse 4800
5 T. Boles 3700
6 J. Schnackenberg 2100
7 . Lauri 1500
8 R. Pattyn 700
9 T. P 600
9 J. PĂ©ran 600
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48407
Total active users1519
Act. users last 24h1
Act. users last hour0
Registered user hits last week239
Registered user hits last month1082
Go up

Unable to refresh mv with out_of_place option (with ORA-12097)
Next thread: Purge 2PC
Prev thread: Notification for a new Java version

Message Score Author Date
Hi, I am working with Oracle RDBMS 12cR1. I ...... Lauri Dec 10, 2018, 10:54
Did you try checking if the table is actually lock...... Ammar Zai Dec 13, 2018, 09:26
Hi, I was unable to find out the issue. The lo...... Lauri Dec 13, 2018, 09:26
@Ammar: yes, not lock(s) were identified, and the ...... Lauri Dec 13, 2018, 09:28

Follow up by mail Click here


Subject: Unable to refresh mv with out_of_place option (with ORA-12097)
Author: Lauri, Netherlands
Date: Dec 10, 2018, 10:54, 250 days ago
Os info: Linux
Oracle info: 12cR1
Error info: ORA-12097, ORA-06512
Message: Hi,

I am working with Oracle RDBMS 12cR1.

I get the following error when I am trying to refresh a materialized view with the Oracle 12 feature:

begin
dbms_mview.refresh(
list => 'MV_OB_REPORT_DETAILS',
method => 'C',
atomic_refresh => false,
out_of_place => true
);
end;
/

---
Error:

Error report -
ORA-12097: changes in the master tables during refresh, try refresh again
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 2
12097. 00000 - "changes in the master tables during refresh, try refresh again"
*Cause: There are some changes (i.e., conventional DML, direct load,
partition maintenance operation) in the master tables during
materialized view refresh.
*Action: Refresh the affected materialized views again.
---

It seems be related to a modification in a master table of the mv... and if I re-run the statement, I always get the same error.
I can query the concerned master table.

Does someone know where should I look to investigate further (I have a developer role for this, and not DBA credentials)?
Did someone face such issue before?

Thanks and Regards
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Unable to refresh mv with out_of_place option (with ORA-12097)
Author: Ammar Zai, Netherlands
Date: Dec 13, 2018, 09:26, 247 days ago
Message: Did you try checking if the table is actually locked while you're trying to refresh the materialized view? (First thing that came to my mind)
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-Unable to refresh mv with out_of_place option (with ORA-12097)
Author: Lauri, Netherlands
Date: Dec 13, 2018, 09:26, 247 days ago
Message: Hi,

I was unable to find out the issue.
The logical definition of the materialized view has been checked and no discrepency has been found.
we have raised an SR.
In the meantime, we use a more classical refresh method that truncates the table.
The response time is acceptable.

Kind Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-Unable to refresh mv with out_of_place option (with ORA-12097)
Author: Lauri, Netherlands
Date: Dec 13, 2018, 09:28, 247 days ago
Message: @Ammar: yes, not lock(s) were identified, and the table was even not queryied by another session except the one performing the refresh.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here