Tag: ORA-01031: insufficient privileges

Oracle Multitenant Database System User ORA-01031: insufficient privileges

DBA is trying to setup a job to purge table “sys.aus$” periodically with following sql and errors:

SQL> show user;

USER is "SYSTEM"

SQL> delete FROM sys.aud$ WHERE  NTIMESTAMP# < sysdate -31;

ERROR at line 1:
ORA-01031: insufficient privileges

Solution

Though SYSTEM user has DBA role privilege, the specific object privilege is still needed in this case:

SQL> grant delete on sys.aud$ to system container=all;

Grant succeeded.

SQL>  delete FROM sys.aud$ WHERE  NTIMESTAMP# < sysdate -31;

2006228 rows deleted.


SQL> commit;

Commit complete.

ORA-00942: table or view does not exist ORA-01031: insufficient privileges while creating materialized view

ORA-00942 or ORA-01031 error occurs while creating a materialized view from a table of another schema.

Source Table : USERA.TABLE_A
Materialized View : USERB.TABLE_A_MV

SQL> show user
USER is "USERB"
SQL>  create materialized view TABLE_A_MV
BUILD IMMEDIATE
REFRESH FAST ON commit
with primary key
as select * from usera.table_a ;
as select * from usera.table_a
                           *
ERROR at line 5:
ORA-12018: following error encountered during code generation for
"USERB"."TABLE_A_MV"
ORA-00942: table or view does not exist

But USERB has no problem to access USERA.TABLE_A:

SQL> show user
USER is "USERB"
SQL> select count(*) from  usera.table_a;
  COUNT(*)
----------
   1623583

If creating the materialized view by sys user, then gets another different error:

SQL> show user
USER is "SYS"
SQL> create materialized view USERB.TABLE_A_MV
BUILD IMMEDIATE
REFRESH FAST ON commit
with primary key
as select * from usera.table_a ;
as select * from usera.table_a
*
ERROR at line 4:
ORA-01031: insufficient privileges

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe
Log in

ORA-31633: unable to create master table SYS_EXPORT_SCHEMA_01

The following errors occur when exporting/importing through data pump:

 ORA-31626: job does not exist
 ORA-31633: unable to create master table "USERNAME.SYS_EXPORT_SCHEMA_01"
 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
 ORA-06512: at "SYS.KUPV$FT", line 1048
 ORA-01031: insufficient privileges

SOLUTION

To make sure user ‘USERNAME’ to have enough privileges to do import/export.

ORA-01031: insufficient privileges by EXECUTE IMMEDIATE

“ORA-01031: insufficient privileges” was received from a PL/SQL procedure  “EXECUTE IMMEDIATE” state.

1)Create a simple PL/SQL procedure.

SQL> create or replace procedure proc_test
is
begin
          EXECUTE IMMEDIATE 'create table tbl_test ( id number)';
end;

2) logon as user ,and then execute above procedure.

SQL> exec proc_test;
BEGIN proc_test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST_USER.PROC_TEST", line 7
ORA-06512: at line 1

3) grant ‘create table’ privilege to user directly.

SQL> show user
USER is "SYS"
SQL> grant create table to test_user;

Grant succeeded.

4)logon as user ,and then execute above procedure.

SQL> show user
USER is "TEST_USER"

SQL> exec proc_test;

PL/SQL procedure successfully completed.

SQL> desc tbl_test;
Name   Null?   Type
----- -------- ----------------------------
ID             NUMBER

SQL> drop table tbl_test;

REASON: In stored procedure/function, any privileges granted by a role will NOT be effective. The privileges should be directly granted to the user.