Square DBA – Oracle DBA Talk

Today I needed to create a materialized view and received an error. My syntax was correct. Here is the create statement.

CREATE MATERIALIZED VIEW SCOTT.STUPID_TABLE
BUILD IMMEDIATE
REFRESH FORCE
START WITH SYSDATE
NEXT trunc(sysdate+1) + 22/24
AS
select * from BILL.STUPID_TABLE@remote1;

and I received this error.

ERROR at line 1:
ORA-06564: object EXTDIR does not exist
ORA-02063: preceding line from remote1

This error was generated because I did not have read/write privileges on the directory EXTDIR. Yes STUPID_TABLE is an external table. To correct the problem simply grant the correct permissions.

GRANT READ ON DIRECTORY EXTDIR to MYJOBUSER;
GRANT WRITE ON DIRECTORY EXTDIR to MYJOBUSER;