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。那位清楚是什么问题造成的权限问题。
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。那位清楚是什么问题造成的权限问题。
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 SYSDATEcreate table scott.semp
(empno number(4) primary key,
ename varchar2(10),
sdate date default SYSDATE;-- Create a Materialized View on a Pre-built tablecreate 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;
Res:
-------
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.
应该没有权限
grant !