V$TIMEZONE_NAMES

Oracle 11gR1
V$TIMEZONE_NAMES displays valid time zone names.

Column	Datatype	Description
TZNAME	VARCHAR2(64)	Time zone region (for example, US/Pacific)
TZABBREV	VARCHAR2(64)	Corresponding daylight abbreviation (for example, PDT)

Notes:

Two time zone files are included in the Oracle home directory. The default time zone file is $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat. A smaller time zone file can be found in $ORACLE_HOME/oracore/zoneinfo/timezone.dat.

-rw-r--r-- 1 oracle dba 408315 Oct 19 2007 timezlrg.dat

-rw-r--r-- 1 oracle dba 160733 Oct 19 2007 timezone.dat

Environment variable: TZ=America/Vancouver

To view the time zone names in the file being used by the database, use the following query:

select * from V$TIMEZONE_NAMES;

TZNAME	TZABBREV
Africa/Algiers	LMT
Africa/Algiers	PMT
Africa/Algiers	WET
Africa/Algiers	WEST
Africa/Algiers	CET
Africa/Algiers	CEST
Africa/Cairo	LMT
Africa/Cairo	EET
Africa/Cairo	EEST
Africa/Casablanca	LMT
Africa/Casablanca	WET
Africa/Casablanca	WEST
……	……

Oracle dynamic performance views

More Oracle DBA tips, please visit Oracle DBA Tips