Hello,
While testing I find out, one of my new scheduler job has failed with
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from ORCL
ORA-06512: at "C##ODB_ADMIN.SP_X", line 3
ORA-06512: at line 1
my job is running on cdb$root and deleting a table in different pdb called “orcl”. This is a 19.16 database and tried on different database servers. I use a common user which has privileges on all containers. here is a sample code:
-- on cdb$root
create table tmp (id number);
alter table tmp add primary key (id);
insert into tmp values (1);
insert into tmp values (2);
insert into tmp values (3);
commit;
create or replace procedure sp_x as
begin
delete containers(tmp)
where id > 5
and con_id = 3;
commit;
end;
/
--on pdb
alter session set container = orcl;
create table tmp (id number);
alter table tmp add primary key (id);
insert into tmp values (5);
insert into tmp values (6);
insert into tmp values (7);
commit;
When I call SP_X procedure from sql developer, it works without error and delete the 6th row on "orcl" pdb.
When I create a scheduler job, job gets “invalid username/password” error:
alter session set container=cdb$root;
select * from containers(tmp) where con_id = 3 and id = 6;
ID CON_ID
---------- ----------
6 3
exec sp_x;
PL/SQL procedure successfully completed.
select * from containers(tmp) where con_id = 3 and id = 6;
no rows selected
-- re-insert the row 6
alter session set container=orcl;
insert into tmp values (6);
commit;
--cdb$root
alter session set container=cdb$root;
exec dbms_Scheduler.create_job('TEST1', job_action => 'begin sp_x; end;' , job_type => 'PLSQL_BLOCK', enabled=>true);
select status, additional_info from user_scheduler_job_run_details
where job_name ='TEST1';
STATUS ADDITIONAL_INFO
-----------------------------------------------------------------------------------------------------
FAILED
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from ORCL
ORA-06512: at "C##ODB_ADMIN.SP_X", line 3
ORA-06512: at line 1
as far as I understand “containers()” clause works with an internal dblink. my listener is on default port (1521) and since this is a multitenant, everything is on same server.
I couldn't find anything in the documentation about a limitation. Do I miss something here?
thanks.
edit: I added “alter session set container=cdb$root;” line before selecting tmp table with containers clause.