The possible causes could be:
- Columns have been added into the master table: In most cases, you know who did it without notifying DBA.
- The master table has been dropped: If the dropping is planned.
- Database link is unavailable. The accounts of database links could be locked, altered or temporarily unreachable due to network problems.
- Unique constraint violated. The index violation happened on the snapshot, not on the master table.
You should: recreate the snapshot.
You should: drop the snapshot.
You should: check the availability of the database link, including account status and network availability.
You should: rebuild the index.
I summarized the solutions as below:
- Make a complete refresh: If the master table is quite small.
- Rebuild the unique index of the snapshot: If the master table is quite large.
- Rereate the snapshot: If DDL of the master table is altered or you had tried all above methods but failed.
- Drop the snapshot: If the master table is no longer existent.
I create snapshot withou the lob column but after refresh i find the lob is included and after more refreshes i get th ora-12008
Please help
Maybe you should recreate it?
I had a column which was a char(1) being converted to a number in the MV SELECT statement. That was enough to blow it up when trying to refresh it.
You can see the invalid number reported here.
ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2370
ORA-01722: invalid number
ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 85
ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 245
That’s what I get for not testing the SQL outside of the MV create script.
To solve type conversion error like ORA-01722, you may check: How to Resolve ORA-01722: invalid number.
The user says “when we try to refresh MVIEW through Kafka job(Java Program). It is taking time and endup in below error.
“ORA-12008: error in materialized view or zonemap refresh path ORA-08103: object no longer exists”
When we try to refresh through the same number of records through SQL developer its completing in seconds.
job run date : 23-JAN-2023 20:38:21
Also I am unable to generate AWR during the time to analyze further. ”
Could you pls suggest.
It looks like an IO problem, the MV might fall into complete refreshing.