You can set the default session time zone with the ORA_SDTZ environment variable. When users retrieve TIMESTAMP WITH LOCAL TIME ZONE data, Oracle returns it in the users' session time zone. The session time zone also takes effect when a TIMESTAMP value is converted to the TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE datatype.
Note:
Setting the session time zone does not affect the value returned by the SYSDATE and SYSTIMESTAMP SQL function. SYSDATEreturns the date and time of the operating system on which the database resides, taking into account the time zone of the database server's operating system that was in effect when the database was started.
The ORA_SDTZ environment variable can be set to the following values:
Operating system local time zone ('OS_TZ')
Database time zone ('DB_TZ')
Absolute offset from UTC (for example, '-05:00')
Time zone region name (for example, 'Europe/London')
To set ORA_SDTZ, use statements similar to one of the following in a UNIX environment (C shell):
% setenv ORA_SDTZ 'OS_TZ'
% setenv ORA_SDTZ 'DB_TZ'
% setenv ORA_SDTZ '-05:00'
% setenv ORA_SDTZ 'Europe/London'
You can change the time zone for a specific SQL session with the SET TIME_ZONE clause of the ALTER SESSION statement.
TIME_ZONE can be set to the following values:
Default local time zone when the session was started (local)
Database time zone (dbtimezone)
Absolute offset from UTC (for example, '+10:00')
Time zone region name (for example, 'Asia/Hong_Kong')
Use ALTER SESSION statements similar to the following:
ALTER SESSION SET TIME_ZONE=local;
ALTER SESSION SET TIME_ZONE=dbtimezone;
ALTER SESSION SET TIME_ZONE='+10:00';
ALTER SESSION SET TIME_ZONE='Asia/Hong_Kong';
You can find out the current session time zone by entering the following query:
SELECT sessiontimezone FROM DUAL;
OCP考题：
[sql]
10. The current time zone for one of the user sessions is set to the
database local time zone. For one application, the user sessionrequi
res the time zone to be set to the local operating system time zone w
ithout affecting other user sessions.
Which two solutions could the user implement to achieve this objectiv
e? (Choose two.)
A. use the ALTER SYSTEM command to change the time zone
B. use the ALTER SESSION command to change the time zone
C. use the ALTER DATABASE command to change the time zone
D. set the value for the operating system variable ORA_SDTZ on the cl
ient machine
E. set the value for the operating system variable ORA_SDTZ on the da
tabase server machine
Answer: B, D
