There are two kinds of timezone:

1. DBTIMEZONE you can set the time zone for database by “alter database set time_zone=’+08:00′”. To make
this take effect, you should reboot the database. But if you have a column that is of TIMESTAMP WITH TIME ZONE/LOCAL TIME ZONE, you will not be able to change the DBTIMEZONE. SYSTIMESTAMP is used to get the current database timestamp with time zone information. BTW, SYSDATE is used to get the current date without time zone information.

2. SESSIONTIMEZONE you can set time zone at the session level
e.g. you can issue “alter session set TIME_ZONE=’+08::00′;” to change the timezone for current session. CURRENT_TIMESTAMP is used to get the current session timestamp with time zone infomation.

There are three types of timestamp:

1. timestamp

2. timestamp with time zone

3. timestamp with local time zone

The key point is “The main idea to grasp here is that the column with datatype TIMESTAMP WITH TIME ZONE stores and displays the explicit time supplied from the INSERT statement. The column with datatype TIMESTAMP WITH LOCAL TIME ZONE stores the explicit time supplied but will display a value that is relative to the current session time zone. This means that if you want hard and fast TIMESTAMPS with time zone information stored, you should use the TIMESTAMP WITH TIME ZONE datatype. If you are concerned more with giving date and time information to customers in different time zones and represented in their local time, you should use the TIMESTAMP WITH LOCAL TIME ZONE datatype.”

references:

http://www.dbasupport.com/oracle/ora9i/TimeZone.shtml

http://toolkit.rdbms-insight.com/tz.php

P.S. you can get all valid timezone name by “select tzname, tzabbrev from V$TIMEZONE_NAMES”