Declare
v_database_database_role VARCHAR2(50) := '';
v_job_database_role VARCHAR2(50) := '';
Begin
-- checking database_role of the job
DBMS_SCHEDULER.GET_ATTRIBUTE ('&scheduler_job_owner.&scheduler_job_name', 'DATABASE_ROLE', v_job_database_role);
DBMS_OUTPUT.PUT_LINE ('Database Role of the concerned job is: ' || NVL(v_job_database_role, 'unknown'));
-- checking the database_role of the database
select DATABASE_ROLE into v_database_database_role from V$DATABASE;
DBMS_OUTPUT.PUT_LINE ('Database Role of the database is: ' || NVL(v_database_database_role, 'unknown'));
-- setting the database_role of the job to that of the database (if not matching)
If ((NVL(upper(v_job_database_role),'') <> NVL(upper(v_database_database_role),'')) and v_database_database_role is not null) Then
DBMS_SCHEDULER.SET_ATTRIBUTE ('&scheduler_job_owner.&scheduler_job_name', 'DATABASE_ROLE', '''' || v_database_database_role || '''');
End IF;
-- checking new database_role of the job
DBMS_OUTPUT.PUT_LINE ('Current Database Role of the concerned job is: ' || NVL(v_database_database_role, 'unknown'));
End;
/
select * from DBA_SCHEDULER_JOBS where OWNER='&scheduler_job_owner' and JOB_NAME='&scheduler_job_name';