OCP考题解析_043: 会话时区和ORA_SDTZ环境变量

撒的阿坎努斯

You can set the defaultsession time zone with theORA_SDTZenvironment variable. When users retrieveTIMESTAMP WITH LOCAL TIME ZONEdata, Oracle returns it in the users' session time zone. The session time zone also takes effect when aTIMESTAMPvalue is converted to theTIMESTAMP WITH TIME ZONEorTIMESTAMP WITH LOCAL TIME ZONEdatatype.

Note:
Setting the session time zone does not affect the value returned by theSYSDATEandSYSTIMESTAMPSQL 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.

TheORA_SDTZenvironment 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 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_ZONEclause of theALTER SESSIONstatement.
TIME_ZONEcan 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 SESSIONstatements 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