Re: error creating materialized view

Malcolm Dew-Jones wrote:
> JS (somewhere_at_dot.com) wrote:
> : HansF wrote:
> : > On Thu, 20 Oct 2005 16:27:38 -0400, JS interested us by writing:
> : >
> : >
> : >>Hi,
> : >>
> : >>I am trying to tune a materialized view with following command
> : >>
> : >>var viewid varchar2(20);
> : >>begin
> : >>dbms_advisor.tune_mview
> : >>(:viewid, 'create or replace materialized view employeeView refresh fast
> : >>enable query rewrite as select firstname from employees');
> : >>end;
> : >>/
> : >
> : > ...
> : >
> : >>What am I missing?
> : >
> : >
> : > A 1 second glance at http://docs.oracle.com in the Oracle SQL Reference
> : > manual under 'CREATE MATERIALIZED VIEW' indicates that the 'OR REPLACE'
> : > syntax is not valid. (However, looking at 'CREATE PACKAGE' I find it does
> : > specify the 'OR REPLACE' is valid but optional.)
> : >
> : > Perhaps it's your syntax?
> : >
> : Now I get
>
> : ORA-20000: Invalid CREATE MATERIALIZED VIEW statement because of:
> : ORA-00942: table or view does not exist
>
> I will guess that the problems is that you are trying to use a table or
> view that does not exist.
>
> So, I ask myself, which tables or views are you trying to use that would
> need to exist? Oh look ...
>
> "from employees"
>
> That thing called "employees" needs to exist - or to be more precise, it
> has to be seen by the job that is running the create statement, which is
> not quite the same thing.
>
> So either create employees first, or, if employees does exist, then you
> need to figure out what things can be "seen" by the code as it runs and
> make it see employees.
>
>
> --
>
> This programmer available for rent.

Not to mention that privileges to employees MUST be granted explicitly ... not through a role.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)