-
Hi
Our customer in Japan has thier UNIX machine set to dallas time. However they would
like to set to Japanese time in the oracle database.
Any idea??
MK
-
1. Shutdown the database.
2. Change time from the OS
3. startup database
Jeff Hunter
-
The session time zone can be set to:
- O/S local time zone
- Database time zone
- An absolute offset
- A named region
1. The first method consists to use one of the following ALTER SESSION SET
TIME_ZONE statements:
SQL> ALTER SESSION SET TIME_ZONE = local;
SQL> ALTER SESSION SET TIME_ZONE = dbtimezone;
SQL> ALTER SESSION SET TIME_ZONE = '-05:00';
SQL> ALTER SESSION SET TIME_ZONE = 'Europe/London';
2. As an alternative, the operating system environment variable ORA_SDTZ can
also be used to set the session time zone:
ORA_SDTZ = 'OS_TZ' | 'DB_TZ' | '[+ | -] hh:mm' | 'timezone_region'
Example:
$ ORA_SDTZ='OS_TZ'
$ export ORA_SDTZ
$ ORA_SDTZ='-05:00'
$ export ORA_SDTZ
-
Stecal,
Is this a 8i or 9i feature
MK
-
There a couple of things that are new for 9i, but select sessiontimezone from dual, for example, works in both.
4. How to set the database time zone ?
======================================
Answer
------
Note that the database time zone is only relevant for TIMESTAMP WITH LOCAL TIME ZONE
columns.
1. At creation time
SQL> CREATE DATABASE ...
SET TIME_ZONE='Europe/London';
If not specified with the CREATE DATABASE statement, the database time zone
defaults to the server’s O/S timezone offset.
2. After database creation, use the ALTER DATABASE SET TIME_ZONE statement and
then shut down and restart the database.
SQL> ALTER DATABASE SET TIME_ZONE = '-05:00';
SQL> ALTER DATABASE SET TIME_ZONE = 'Europe/Lisbon';
The change will not take effect until the database is bounced.
5. How to list the valid time zone regions ?
============================================
Answer
------
SQL> SELECT * FROM v$timezone_names;
TZNAME TZABBREV
------------------------------ ----------
Pacific/Tahiti LMT
Poland LMT
US/Pacific PST
Europe/Zurich CET
...
The result will depend on which timezone file is currently in use.
See Q&A 7-8 for further details.
6. How to retrieve the time zone offset corresponding to a time zone region ?
=============================================================================
The new 9i TZ_OFFSET() SQL function returns the time zone offset displacement
to the input time zone region.
SQL> SELECT TZ_OFFSET('Europe/London') FROM DUAL;
TZ_OFFS
-------
+01:00
The returned offset depends on the date this statement is executed.
For example, in the 'US/Pacific' time zone, it may return '-07:00' or '-08:00'
whether daylight saving is in effect or not.
SQL> SELECT TZ_OFFSET(SESSIONTIMEZONE), TZ_OFFSET(DBTIMEZONE) FROM DUAL;
returns the time zone offset corresponding to the time zone set for session
and database.
TZ_OFFS TZ_OFFS
------- -------
+01:00 +00:00
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|