ORA-01882: timezone region %s not found

Error:  ORA-01882: timezone region %s not found

When: accessing dba_scheduler_jobs view

Where: Pl Sql developer



Cause: Inappropriate values of the time zone for the columns with data type as TIMESTAMP. 

Initial suspect may goes to NLS or TIME_ZONE session parameters as this issue appears in third party tool only

col START_DATE  for a40
col END_DATE for a39
col LAST_START_DATE for a39
col LAST_RUN_DURATION for a39
select owner,JOB_NAME,START_DATE,LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE,SCHEDULE_LIMIT,MAX_RUN_DURATION from dba_scheduler_jobs;

OWNER  JOB_NAME START_DATE LAST_START_DATE LAST_RUN_DURATION NEXT_RUN_DATE SCHEDULE_LIMIT MAX_RUN_DURATION
---- ------- ------- --------- ---------- ---------------- -------------- ---------------  ------------- ----------------- 
SYS   PURGE_LOG  28-MAY-10 03.00.00.600000 AM PST8PDT 16-DEC-13 03.00.00.296403 AM PST8PDT +000000000 00:00:01.137385  17-DEC-13 03.00.00.300000 AM PST8PDT
                                                                


PST8PDT TZ found to be causing error


Solution:




1. Use below time zone conversion as a workaround for all columns with TIMESTAMP data type which appeared to be giving problem

select OWNER,job_name,JOB_ACTION,start_date at time zone 'Asia/Calcutta', LAST_START_DATE at time zone 'Asia/Calcutta' , STATE from dba_scheduler_jobs ;

2.Modify the value to reflect correct timezone value



exec dbms_scheduler.disable('PURGE_LOG');
exec dbms_scheduler.enable('PURGE_LOG');
exec dbms_scheduler.run_job('PURGE_LOG');


Above three activities will reset the column values for START_DATE,NEXT_RUN_DATE but LAST_START_DATE wont get reset. 

To reset LAST_START_DATE value perform below steps :


Identify the ddl for dba_scheduler_jobs view to get the exact internal view being used to populate LAST_START_DATE column

set long 9000
select dbms_metadata.get_ddl('VIEW','DBA_SCHEDULER_JOBS') from dual;


Identify the value from internal view sys.scheduler$_job

select last_start_date from sys.scheduler$_job where  PROGRAM_ACTION like '%PURGE_LOG%';


Update the value & set as null

update sys.scheduler$_job set LAST_START_DATE='' where  PROGRAM_ACTION like '%PURGE_LOG%';









Verify if no column values have wrong time zone & re-try from third party