Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Fast Refresh Materialized View - failure

606275Nov 16 2007 — edited Nov 19 2007
Okay, 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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 17 2007
Added on Nov 16 2007
8 comments
1,250 views