Timezone Change

Last DDL date

World Default Date

Timestamp in insert statement

careful setting timezone

To change the Oracle timezone

Answer:  First, see my notes on Oracle timezone. To see your local time zone, enter:

[code language=”sql”]
select
   sessiontimezone
from
   dual;
[/code]

The time_zone is normally set at database creation time:

[code language=”sql”]
create database . . .     set time_zone=’+00:00′;
[/code]

To change the Oracle timezone for an existing Oracle database, change the time_zone parameter and then bounce the database.

[code language=”sql”]
alter database set time_zone = ‘-05:00′;
shutdown immediate
startup
[/code]

For OMS, log into the OMS repository as the SYSMAN user, the above command ’emctl resetTZ agent’ tells you exactly what needs to be executed in the repository.

SQL> exec mgmt_target.set_agent_tzrgn(‘‘, ‘Etc/GMT-6’);
SQL> commit;
SQL> emctl start agent — (or emctl start dbconsole)

Reference:

MOSC: Timestamps & time zones – Frequently Asked Questions Doc ID: Note:340512.1