Oracle timezone: dbtimezone & sessiontimezone

Hi All,  I am trying to understand what is difference between dbtimezone & sessiontimezone.  

To start with Oracle has four datetime data types :

DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE (TSTZ), TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ).

Also we need to know about V$TIMEZONE_NAMES. 

V$TIMEZONE_NAMES has two columns:

TZNAME :  The time zone region (for example, US/Pacific) 

TZABBREV: The corresponding daylight abbreviation (for example, PDT)

So, DBTIMEZONE is a function which returns the current value of Database Time Zone.  It can be checked as below query: 

SQL> select  dbtimezone from dual;

DBTIME

------

+00:00

Thus in need of day light saving, we must set default_timezone attribute to a regions name instead of absolute time zone offset, in order to ensure that daylight saving adjustments are being taken into the application account. 

Time zone is set during database creation or when using CREATE DATABASE. It can be altered using ALTER DATABASE command.

SQL> ALTER DATABASE SET TIME_ZONE='America/Menominee';

Database altered.

SQL> ALTER DATABASE SET TIME_ZONE='-06:00';

Database altered.

SQL> SELECT TZ_OFFSET('America/Menominee') FROM DUAL;

TZ_OFFS

--------

-06:00

Also I need to add when we set TIME ZONE database must be re-start.

DBTIMEZONE and SESSIONTIMEZONE are different in their operational scope. DBTIMEZONE shows the database time zone, while SESSIONTIMEZONE shows it for the session. This implies that if the time zone is altered at session level, only SESSIONTIMEZONE will change and not the DBTIMEZONE.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

-------------------------------------------------------------

+05:30

SQL> ALTER SESSION SET TIME_ZONE = 'US/Pacific';

Session altered.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

-------------------------------------------------------------

US/Pacific

SQL>