How can I alter the date and hour of the database? I don't want to alter the hour and date from the server, only in database.
1 Answer
set_time_zone_clause
This clause has the same semantics in CREATE DATABASE and ALTER DATABASE statements. When used in with ALTER DATABASE, this clause resets the time zone of the database. To determine the time zone of the database, query the built-in function DBTIMEZONE. After setting or changing the time zone with this clause, you must restart the database for the new time zone to take effect.
Oracle Database normalizes all new TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk.Oracle Database does not automatically update existing data in the database to the new time zone. Therefore, you cannot reset the database time zone if there is any TIMESTAMP WITH LOCAL TIME ZONE data in the database. You must first delete or export the TIMESTAMP WITH LOCAL TIME ZONE data and then reset the database time zone. For this reason, Oracle does not encourage you to change the time zone of a database that contains data.
For a full description of this clause, refer to set_time_zone_clause in the documentation on CREATE DATABASE.
SQL> select dbtimezone from dual;
DBTIME
------
+02:00
SQL> ALTER DATABASE SET TIME_ZONE = '-05:00';
Database altered.
SQL> select dbtimezone from dual;
DBTIME
------
+02:00
SQL> startup force
ORACLE instance started.
Total System Global Area 536868920 bytes
Fixed Size 8898616 bytes
Variable Size 226492416 bytes
Database Buffers 293601280 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select dbtimezone from dual;
DBTIME
------
-05:00
answered Aug 11, 2019 at 18:03
DBTIMEZONE) - however most likely it will not bring the effect you would expect, see stackoverflow.com/questions/29271224/…