ORA-01031: insufficient privileges解决

最近写了个存过需要建表,并通过job调用执行,发现单独执行存过是没有问题,一用job调用执行就会报ORA-01031: insufficient privileges错误.先是查看当前用户的权限,是UNLIMITED TABLESPACE,于是又增加了CREATE ANY TABLE权限,继续执行还是报一样的错误.既然权限没有问题,为什么还是报错了,想了想应该是当前的session认不到,于是在执行execute immediate 'create table..'语句前增加了 execute immediate 'set role ALL';语句,再次执行job,执行成功.问题解决!

 

备注:

Use the SET ROLE statement to enable and disable roles for your current session.

When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user's default roles. During the session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. The number of roles that can be concurrently enabled is limited by the initialization parameter MAX_ENABLED_ROLES.

You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.

You can use it like:
set role none;
set role all;
......
set role dba;