Hi there gurus...
Version: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
As a test of something I wish to do, I'm submitting the following CREATE TABLE DDL statement as a job from within a schema over a dblink:
declare
v number;
begin
dbms_job.submit@GL_UPDATE_DBA(v,'
declare v varchar2(2000);
begin
execute immediate ''create table tempcb2 (x number)'';
exception when others then v := SQLERRM; insert into tempcb1 (x) values (v); commit;
raise;
end;');
commit;
end;
The DB Link GL_UPDATE_DBA connects to a remote DB instance using a schema called SUPPORT_DBA.
When I run this code, the job is submitted fine within SUPPORT_DBA schema on the remote DB. However, an exception is captured and written to the table TEMPCB1 stating: ORA-01031: insufficient privileges.
When I check the job details on the remote DB using
SELECT * FROM USER_JOBS
in the SUPPORT_DBA schema, the LOG_USER, PRIV_USER and SCHEMA_USER all list the proper value: SUPPORT_DBA.
From the documentation description of PRIV_USER, this job should be running with the privileges of SUPPORT_DBA and therefore, to my feable mind at least(!), should be able to create a table within that schema.
Why is the job unable to create a table in the SUPPORT_DBA schema?? Have I misinterpreted the way jobs (and their associated privs) work??
Thanks in advance...