By gayan Database 23ai now introduces the autonomy for each PDBs to control their time zone settings independently. This will enhance the overall capabilities of consolidated multitenant databases that host many self-contained pluggable databases.
Database functions such as SYSDATE and SYSTIMESTAMP, querying data from DATE and TIMESTAMP columns will have the impact on the new feature implementation.
TIME_AT_DBTIMEZONE
This new parameter can take one of the following values
- OFF: The behavior of pre-23ai. Time-related operations will be based on the database hosts Time zone values.
- USER_SQL: Functions SYSDATE and SYSTIMESTAMP will be based on the DBTIMEZONE value in the PDB
- DATABASE: All time-related operations will depend on the set PDB DBTIMEZONE value.
Demonstration
My current PDB TIMEZONE value is UTC. (The Value derived from the DB host)
I will now add a table with DATE and TIMESTAMP columns
As expected values will be stored based on UTC
The DBTIMEZONE will be changed to ASIA/SINGAPORE at the PDB level
I will first set the new parameter value to USER_SQL
Lets query time from DUAL
As expected, the returned values are based on the PDB timezone setting for functions such as SYSDATE and SYSTIMESTAMP
Lets change the parameter to DATABASE now
And perform an insert
As You could see, the new insert is based on the DBTIMEZONE value.