关于创建materialized view 的问题

[zdljj]

create materialized view MEDIA_TYPE
pctfree 10
pctused 40
initrans 1
maxtrans 255
tablespace MYDATA
storage
(
initial 32M
minextents 1
maxextents unlimited
)
refresh complete on demand
enable query rewrite
AS
select * FROM alert_entries a ,mapping b
WHERE to_char(a.creationdate, 'yyyymmdd') = to_char(SYSDATE-1,'yyyymmdd') ;
ORA-01031: insufficient privileges

当我执行如下命令时候就没有问题
create materialized view MEDIA_TYPE
tablespace MYDATA
AS
select * FROM alert_entries a ,mapping b
WHERE to_char(a.creationdate, 'yyyymmdd') = to_char(SYSDATE-1,'yyyymmdd') ;

搞不清楚是为什么。就算那段可以执行的程序在一个package中调用。有一台服务器正常另外台也会报ORA-01031: insufficient privileges。那位清楚是什么问题造成的权限问题。

[liuyi8903]

Materialized View's defining query can not contain references to non-repeating
expressions like SYSDATE and ROWNUM when using options like QUERY REWRITE or
FAST REFRESH.


Create a Materialized View on a Pre-built table which includes a date field
defaulted to the SYSDATE.

The table must have the same name and be in the same schema as the resulting
Materialized View.

If the Materialized View is dropped, the pre-existing table reverts to its
identity as a table.

Example:
-------
-- Create a Pre-built table including a date field defaulted to SYSDATE

create table scott.semp
(empno number(4) primary key,
ename varchar2(10),
sdate date default SYSDATE;

-- Create a Materialized View on a Pre-built table

create materialized view scott.semp
on prebuilt table
as select empno,ename from scott.emp;

-- In case of a fast refresh, this workaround will only work for inserted rows
-- but not for updated rows. Create a Trigger for Update to workaround.

create materialized view scott.semp
on prebuilt table
refresh fast
as select empno,ename from scott.emp;

create or replace trigger scott.semp_update
before update on scott.semp
for each row
declare
sdate date := sysdate;
begin
:new.sdate := sdate;
end;
Remarks:
-------
The ON PREBUILT TABLE clause just registers an existing table as a
pre-initialized Materialized View that means that a complete refresh of the
master table is NOT done automatically.

[liuyi8903]

以上来自metalink Note:176213.1

[cosio]

ORA-01031 insufficient privileges

Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges.

Action: Ask the database administrator to perform the operation or grant the required privileges.


应该没有权限
grant !