A client try to create a materialized view by SYS user with the following errors:
SQL> show user USER is "SYS" SQL> create materialized view USER1.MV_TABLE2 BUILD IMMEDIATE REFRESH FAST ON commit with primary key as select * from USER2.TABLE2; ERROR at line 6: ORA-01031: insufficient privileges
Read more of this content when you subscribe today.
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
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 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
Read more of this content when you subscribe today.
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
To make sure user ‘USERNAME’ to have enough privileges to do import/export.
“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.
Make DBA Life Easy 