Home » SQL & PL/SQL » SQL & PL/SQL » creating a materialized view throws "ORA-01031: insufficient privileges" (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
creating a materialized view throws "ORA-01031: insufficient privileges" [message #681410] Wed, 15 July 2020 10:29 Go to next message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
I am attempting to create a materialized view. The based table is the same owner as the materialized view to be created. When attempted to create a materialized view it throws "ORA-01031: insufficient privileges". However, when the same query is used to create a simple view it is working. Please advise. Thank you.

SQL> select substr(owner,1,20) owner, substr(object_name,1,20) object_name,
  2         substr(object_type,1,20) object_name
  3    from dba_objects
  4   where object_name = 'SSP_EI'
  5     and owner       = 'SSOWNER';

OWNER                OBJECT_NAME          OBJECT_NAME
-------------------- -------------------- --------------------
SSOWNER              SSP_EI               TABLE

SQL>

SQL> select privilege
  2    from dba_sys_privs where grantee = 'SSOWNER';

PRIVILEGE
----------------------------------------
CREATE MATERIALIZED VIEW

SQL> CREATE MATERIALIZED VIEW LOG ON SSOWNER.SS_INC WITH PRIMARY KEY INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW ssowner.ssp_ei_MV
  2    NOLOGGING
  3    CACHE
  4    BUILD IMMEDIATE
  5    REFRESH FAST ON COMMIT  AS
  6              SELECT EI.*
  7                FROM ssowner.ssp_ei EI
  8               WHERE Nvl(EI.Modify_Ts, EI.Create_Ts) > '01-JAN-19';
             WHERE Nvl(EI.Modify_Ts, EI.Create_Ts) > '01-JAN-19'
                                                     *
ERROR at line 8:
ORA-01031: insufficient privileges

SQL>
Re: creating a materialized view throws "ORA-01031: insufficient privileges" [message #681412 is a reply to message #681410] Wed, 15 July 2020 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Does your use has sufficient quota on the target tablespace?

SQL> grant CREATE MATERIALIZED VIEW to test identified by test;

Grant succeeded.

SQL> alter user test quota unlimited on ts_d01;

User altered.

SQL> create table test.t (col number primary key);

Table created.

SQL> CREATE MATERIALIZED VIEW LOG ON test.t WITH PRIMARY KEY INCLUDING NEW VALUES;

Materialized view log created.

SQL> alter user test quota 0 on ts_d01;

User altered.

SQL> CREATE MATERIALIZED VIEW  test.mv NOLOGGING CACHE BUILD IMMEDIATE REFRESH FAST ON COMMIT  AS
  2  select * from test.t
  3  /
select * from test.t
                   *
ERROR at line 2:
ORA-01031: insufficient privileges
Note that "Nvl(EI.Modify_Ts, EI.Create_Ts) > '01-JAN-19';" is more likely a bug:
SQL> select to_date('01-JAN-19') from dual;
select to_date('01-JAN-19') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Re: creating a materialized view throws "ORA-01031: insufficient privileges" [message #681413 is a reply to message #681412] Wed, 15 July 2020 10:56 Go to previous messageGo to next message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
I removed the where clause and I am still getting the same error.

SQL> CREATE MATERIALIZED VIEW ssowner.ssp_ei_MV
  2    NOLOGGING
  3    CACHE
  4    BUILD IMMEDIATE
  5    REFRESH FAST ON COMMIT  AS
  6              SELECT EI.*
  7                FROM ssowner.ssp_ei EI;
              FROM sspowner.ssp_ei EI
                            *
ERROR at line 7:
ORA-01031: insufficient privileges
Re: creating a materialized view throws "ORA-01031: insufficient privileges" [message #681414 is a reply to message #681413] Wed, 15 July 2020 11:11 Go to previous messageGo to next message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
apparently it is missing the system privileges "grant create table". looks like it is working now.

thank you.
Re: creating a materialized view throws "ORA-01031: insufficient privileges" [message #681415 is a reply to message #681410] Wed, 15 July 2020 11:30 Go to previous messageGo to next message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
wtolentino wrote on Wed, 15 July 2020 10:29
<snip>
SQL> CREATE MATERIALIZED VIEW ssowner.ssp_ei_MV
  2    NOLOGGING
  3    CACHE
  4    BUILD IMMEDIATE
  5    REFRESH FAST ON COMMIT  AS
  6              SELECT EI.*
  7                FROM ssowner.ssp_ei EI
  8               WHERE Nvl(EI.Modify_Ts, EI.Create_Ts) > '01-JAN-19';
             WHERE Nvl(EI.Modify_Ts, EI.Create_Ts) > '01-JAN-19'
                                                     *
ERROR at line 8:
ORA-01031: insufficient privileges

SQL>
Aside from your immediate quesiton ..
If (as they should be) EI.Modify_Ts, EI.Create_Ts are DATE or TIMESTAMP, you are comparing them to a string: '01-JAN-19'. This can lead to unexpected errors. You should compare DATE to DATE and TIMESTAMP to TIMESTAMP:

WHERE Nvl(EI.Modify_Ts, EI.Create_Ts) > to_timestamp('01-JAN-19','dd-MON-rr')
But even with that, you are recreating the Y2k bug. Always, always, always use 4-digit years:

WHERE Nvl(EI.Modify_Ts, EI.Create_Ts) > to_timestamp('01-JAN-2019','dd-MON-yyyy')
Re: creating a materialized view throws "ORA-01031: insufficient privileges" [message #681416 is a reply to message #681415] Wed, 15 July 2020 12:56 Go to previous message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
thank you. i'll let know our application developer who wrote the code.
Previous Topic: How to Inherit data default from %TYPE attribute for a variable
Next Topic: Sorting of columns in alternative order.
Goto Forum:
  


Current Time: Mon Sep 21 07:48:29 CDT 2020