• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to see the OS timezone used when the database was started

How to see the OS timezone used when the database was started

February 7, 2023 by techgoeasy Leave a Comment

Sometimes we might have different OS timezone settings while starting the database. Also, it could be possible that srvctl is using different timezone settings while starting the Database and this will impact the Database timezone used. You can use the below method to find currently used OS timezone for background processes

For 11.2 and up:
this will give the time and the OS timezone for the last 20 startups in descending order

conn / as sysdba
SET SPACE 1 LINESIZE 80 PAGES 1000
SELECT * FROM (
select to_char(ORIGINATING_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS TZH:TZM')
from V$DIAG_ALERT_EXT
WHERE trim(COMPONENT_ID)='rdbms'
and MESSAGE_TEXT like ('PMON started with%')
order by originating_timestamp desc )
WHERE rownum < 20; 

For 11.1 and lower

This will give the current OS setting of the database background processes ( this should match the OS timezone of the last database startup):

conn / as sysdba
spool /tmp/jobtime.txt
select to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS TZR') from dual;
select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual;
Drop table SYS.SYSTIME_TEST purge;
create table SYS.SYSTIME_TEST (col1 varchar2(30 char));
begin
DBMS_SCHEDULER.DROP_JOB ('SYSTIME_TEST_JOB');
end;
/
begin
dbms_scheduler.create_job('SYSTIME_TEST_JOB','PLSQL_BLOCK','begin insert into SYS.SYSTIME_TEST select to_char(systimestamp, ''DD/MM/YYYY HH24:MI:SS TZR'') from dual; insert into SYS.SYSTIME_TEST select to_char(sysdate, ''DD/MM/YYYY HH24:MI:SS'') from dual;  insert into SYS.SYSTIME_TEST select SESSIONTIMEZONE from dual; commit; end;',start_date=>null, enabled=>true,repeat_interval => null);
end;
/
exec dbms_lock.sleep(5);
select * from SYS.SYSTIME_TEST;
Drop table SYS.SYSTIME_TEST purge;
spool off

The output of the select will give the *current* OS time and timezone seen by the oracle background processes.
The output from SYSTIME_TEST will give the timezone in which the database was started

Hope this article helps in troubleshooting the issues. Please do provide Feedback to improve
Related Article
how to check timezone in oracle database

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Oracle tkprof utility
  • What is Skew in Database?
  • how to find file based on size in linux
  • SQL trace, 10046 event in Oracle
  • How to use sed replace string in file

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us