got ORA-27486: insufficient privileges in procedure.
We have a prod database that contains a schema procedure to run for purge daily. The procedure has to submit job in multi-thread run. We made the migration of the database by running expdp dump file schema by schema from 10g to 11g (11.2.0.3) in oracle enterprise edition. However after the migration we got this message when running the procedure. The owner of the schema is dxpsoa_gsc and the procedure is called by sys user as this: exec DXPSOA_GSC.p_esb_purge(${THREADS},${COMMIT}) , passing the number of thread and number of rows to purge commit parameter. The normal parameters would be 4 thread and 100. I believe the statement caused the error:
dbms_scheduler.create_job('esb_purge_'||to_char(v_start_row)||'_'||to_char(v_end_row),job_type=>'plsql_block',job_action=>v_sql,enabled=>true);
ORA-27486: insufficient privileges
ORA-27486 ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at "DXPSOA_GSC.P_ESB_PURGE", line 74
ORA-06512: at line 9
When I look up the detail from the test run:
insufficient privileges
Cause: An attempt was made to perform a scheduler operation without the required privileges.
Action: Ask a sufficiently privileged user to perform the requested operation, or grant the required privileges to the proper user(s).
I have granted all necessary permissions to the user dxpsoa_gsc, even DBA, SCHEDULER_ADMIN, we still get this error. Why? Remember, this is called from sys user as DXPSOA_GSC.p_esb_purge(${THREADS},${COMMIT}) . And this procedure works fine before the 11g migration.
Please help for what to do, thanks!
This is the procedure for reference.
(
p_threads in number default 1,
p_commit_num in number default 1000) as
v_thread_counter number(2);
v_max_purge_id number(8);
v_threads number(2);
v_start_row number(8);
v_end_row number(8);
v_sql varchar2(2000);
v_running number(2);
v_master_deletions number(8);
begin
for retention_rec in (select component,master_days from DXPSOA_GSC.t_esb_retention) loop
delete from DXPSOA_GSC.esb_dataready_queue
where last_update < (sysdate-retention_rec.master_days)
and status = '!PURGED'
and component=retention_rec.component;
v_master_deletions:=SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE (retention_rec.component||' Master Rows Purged older than '||
to_char(retention_rec.master_days)||' days: '||to_char(v_master_deletions));
commit;
end loop;
execute immediate 'truncate table DXPSOA_GSC.t_esb_purge';
insert into DXPSOA_GSC.t_esb_purge
(select rownum as purge_id,t1.drq_id,t1.component
from DXPSOA_GSC.esb_dataready_queue t1, DXPSOA_GSC.t_esb_retention t2
where t1.last_update < (sysdate-t2.child_days) and t1.status <> '!PURGED'
and t1.component=t2.component);
commit;
select nvl(max(purge_id),0) into v_max_purge_id from DXPSOA_GSC.t_esb_purge;
if v_max_purge_id=0 then
DBMS_OUTPUT.PUT_LINE ('ORA-Nothing to purge - Exiting');
return;
end if;
select count(*) into v_running from all_scheduler_jobs where
state='RUNNING' and job_name like 'ESB_PURGE%';
if v_running>0 then
DBMS_OUTPUT.PUT_LINE ('ORA-Already running - Exiting');
return;
end if;
--NB if threads greater than job_queue_processes then will slow down completion time
v_threads := p_threads;
if (v_max_purge_id/v_threads) < p_commit_num then --changed from 50
-- min rows per thread
v_threads:=1;
DBMS_OUTPUT.PUT_LINE ('ORA-Not much to delete - Just one thread started');
end if;
for v_thread_counter in 1 .. v_threads loop
v_start_row:=ceil((v_max_purge_id/v_threads)*(v_thread_counter-1));
v_end_row:=ceil((v_max_purge_id/v_threads)*v_thread_counter);
v_sql:='p_esb_purge_thread('||to_char(v_start_row)||','||to_char(v_end_row)||','||to_char(p_commit_num)||');';
DBMS_OUTPUT.PUT_LINE ('Running:'||v_sql);
dbms_scheduler.create_job('esb_purge_'||to_char(v_start_row)||'_'||to_char(v_end_row),job_type=>'plsql_block',job_action=>v_sql,enabled=>true); <= this should be one got the error.
end loop;
end;