Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
We are continuously getting an odd error in materialized views refresh, after importing data from production database to development using impdp utility. (Note: We did not get this error in the past years after the successful import and we did not get on QA server after the import now, though). While we are able to successfully drop and recreate the M views, the refresh fails for most of them. It is a kind of common error saying getting a null value for NOT null column (ORA-01400). Below is the error we got for one of the views in the alert.log.
{code}
Errors in file /u01/app/oracle/diag/rdbms/xxxd3db/xxxd3db/trace/xxxd3db_j002_31531.trc:
ORA-12012: error on auto execute of job 3796
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into ("xxx_DATA"."MV_VR_MY_MVIEW_NAME"."SRP_DATE")
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
{code}
However, it did not say the error when recreating the MV after a drop. The M view definition query also did not return a null value for the column reported on the error.
As the alert log mentioned that the error is reported in the said trace file, I checked the trace file output, but couldn't get any reason for the cause.
{code}
TKPROF: Release 11.2.0.1.0 - Development on Mon Mar 26 03:25:58 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Trace file: C:\Users\natarajan_s\Downloads\xxxd3db_j002_31531.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: C:\Users\natarajan_s\Downloads\xxxd3db_j002_31531.trc
Trace file compatibility: 11.1.0.7
Sort options: default
2 sessions in tracefile.
0 user SQL statements in trace file.
0 internal SQL statements in trace file.
0 SQL statements in trace file.
0 unique SQL statements in trace file.
43 lines in trace file.
0 elapsed seconds in trace file.
{code}
I then tried to refresh through Enterprise Manager, it allowed to do for some views only if I did it with 'consider fresh' (ALTER MATERIALIZED VIEW MV_VR_XXXX CONSIDER FRESH), but still failed for others.
Is there any specific reason for this issue and any possible solution to overcome?
Note: All these M views are created with COMPLETE ON DEMAND option.
Advance Thanks.
Regards,
Natarajan