Fast Refresh Materialized View - failure
606275Nov 16 2007 — edited Nov 19 2007Okay, this issue currently has me stumped. I searched the world wide web and still cannot seem to identify the cause of this issue.
Here is the scenario:
I created a materialized view log for the source table (let's say table1) found at DB1.
I then create a materialized view (fast refresh, with logging) at a remote database (let's say DB2). The query for the MV is basic: select * from schema1.table1@db1_link;
I set the materialized view to refresh every 30 seconds (for testing purposes).
This creates a dbms job that executes every 30 seconds. The materialized view gets created successfully. I purposely enter new data into table1. The materialized view log for table1 successfully captures these changes. However, the materialized view found at DB2 does not get refreshed (updated) successfully.
In fact, the dbms job errors-out. It keeps failing and failing. After 16 times, it gets marked as 'broken.'
The error message is as such:
..
RDBA WARNING: ORA-12012: error on auto execute of job 1472
RDBA WARNING: ORA-00604: error occurred at recursive SQL level 3
RDBA WARNING: ORA-01017: invalid username/password; logon denied
RDBA WARNING: ORA-02063: preceding line from db1
RDBA WARNING: ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
RDBA WARNING: ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
RDBA WARNING: ORA-06512: at "SYS.DBMS_IREFRESH", line 683
RDBA WARNING: ORA-06512: at "SYS.DBMS_REFRESH", line 195
RDBA WARNING: ORA-06512: at line 1
---------------
The strange thing is, that the error supposedly claims invalid username/password. But I've used the db links successfully before & the username/password is not invalid. Has anyone encountered this issue before?