Passionate about data

Data and its implications on software design and development.

Materialized Views and Database Links in Oracle.

Recently one of my colleague Jeff Norris had a weird error. He was trying to build a materialized view over some tables in his local database and some tables in his remote database using database links the sql to create the view ran fine and provided the results as expected, but when put inside a materialized view statement complained with ORA-00942 errors. Lets say the two databases in question are local and remote, so the sql to create the materialized view to load immediately and refresh everyday is

1
2
3
4
5
6
7
8
9
10
11
CREATE MATERIALIZED VIEW MV_CUSTOMERBALANCE
BUILD IMMEDIATE
REFRESH FORCE START WITH ROUND(SYSDATE) + 23/24
NEXT SYSDATE + 1
AS
SELECT customer.name , account.balance, accounttype.name
FROM customer , account@remotedb account, accounttype@remotedb accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id
/

Oracle started to complain when creating the above materialized view issuing an error ORA-00942: table or view does not exist, but the SQL without the create materialized view command ran fine giving the expected results.

1
2
3
4
5
6
SELECT customer.name , account.balance, accounttype.name
FROM customer , account@remotedb account, accounttype@remotedb accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id
/

After some searching around and experimenting I found, in the create materialized view statement the database link name can be used only once, which meant we can only use the “remotedb” name once, we got around this restriction by creating two database links to the remote database as REMOTEACCOUNT and REMOTEACCOUNTTYPE and using them in the creation of the materialized view as shown below.

1
2
3
4
5
6
7
8
9
10
11
CREATE MATERIALIZED VIEW MV_CUSTOMERBALANCE
BUILD IMMEDIATE
REFRESH FORCE START WITH ROUND(SYSDATE) + 23/24
NEXT SYSDATE + 1
AS
SELECT customer.name , account.balance, accounttype.name
FROM customer , account@remoteaccount account, accounttype@remoteaccounttype accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id
/