You can set the defaultsession time zone with theORA_SDTZ
environment variable. When users retrieveTIMESTAMP WITH LOCAL TIME ZONE
data, Oracle returns it in the users' session time zone. The session time zone also takes effect when aTIMESTAMP
value is converted to theTIMESTAMP WITH TIME ZONE
orTIMESTAMP WITH LOCAL TIME ZONE
datatype.
Note:
Setting the session time zone does not affect the value returned by theSYSDATE
and
SYSTIMESTAMP
SQL function.
SYSDATE
returns 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.
TheORA_SDTZ
environment variable can be set to the following values:
Absolute offset from UTC (for example,
'-05:00'
)Time zone region name (for example,
'Europe/London'
)
To setORA_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 theSET TIME_ZONE
clause of theALTER 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'
)
UseALTER 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考题:
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