Oracle

alter database set time_zone doesn’t work

Hello, We have a Oracle database 12c  run on Sun Solaris Server, the Unix server timezone is set to ‘EST'(Eastern Standard Time). When we created the database, the db timezone wasn’t explicitly specified. So when I issued the following statement:
“Select dbtimezone from dual”
I got +02:00

Syntax :

ALTER DATABASE <my db name> SET TIME_ZONE = ‘US/Eastern’
or
ALTER DATABASE <my db name> SET TIME_ZONE = ‘EST’

What do I do to change my Oracle timezone?

clear@db> select dbtimezone from dual;
DBTIMEZONE
——
-02:00

clear@db> alter database set time_zone = ‘EST’;
Database altered.

clear@db> select dbtimezone from dual;
DBTIMEZONE
——
-03:00
clear@db> connect / as sysdba;
Connected.
clear@db> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
clear@db> startup
ORACLE instance started.

Total System Global Area 358909124 bytes
Fixed Size 279748 bytes
Variable Size 335544320 bytes
Database Buffers 16777216 bytes
Redo Buffers 6307840 bytes
Database mounted.
Database opened.

clear@db> select dbtimezone from dual;
DBTIMEZONE
—-
EST

*******

For OMS( Oracle Management Server ) , 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)

 

Br. Erman.

Advertisements
Standard