oracle: ocp题解与实验(10/205) .

 题目：

10. The current time zone for one of the user sessions is set to the database local time zone. 
For one application, the user session requires the time zone to be set to the local operating 
system time zone without affecting other user sessions.
Which two solutions could the user implement to achieve this objective? (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 client machine
E. set the value for the operating system variable ORA_SDTZ on the database server machine
Answer: B, D

题目翻译：其中一个用户会话当前的时区设置的是数据库本地时区database local time zone，此时另一个会话为了一个应用程序，需要将时区设置为该程序所在操作系统的本地系统时区，而不影响其他用户会话时区设置。哪两种解决方案可以达到这个目的？

答案解释：是关于会话时区设置的，首先要区分一个system、session、database，对于时区来讲只有database和session级别的没有system一说，所以A明显是错误的，根据题意应是描述session级别的时区设置，而不是database级别时区设置。session时区设置先看下官方文档怎么说的

Setting the Session Time Zone

You can set the default session 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 the TIMESTAMP WITH TIME ZONE orTIMESTAMP WITH LOCAL TIME ZONE datatype.

Note:

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

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 theSET 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;

通过上面可以知道有两种方法：

1、通过设置应用程序的环境变量，在Unix的方法是export ORA_SDTZ='OS_TZ'；

2、通过sql语句改变session的时区，方法是：alter session set time_zone=local；