1. At creation time
SQL> CREATE DATABASE ...
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.
select dbtimezone from dual;
select sessiontimezone from dual;
select new_time(sysdate,to_char(current_timestamp,'TZD'),'GMT') from dual; -- this is for US/Easter Time Stamp
One server will have many instances which will point to different database.
Ex: India, Singapore and Australia.
When I connect to Singapore database and give select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual, it should give me India time and if I do the same thing from Singapore instance then, it should give me Singapore time.
Is this possible.
According to Prakashs43,
I did the following:
1. SQL> select dbtimezone from dual ;
The database was created without specifying the TIME_ZONE clause.
5. SQL> select new_time(sysdate,to_char(current_timestamp,'TZD'),'GMT') from dual;
select new_time(sysdate,to_char(current_timestamp,'TZD'),'GMT') from dual
ERROR at line 1:
ORA-01857: not a valid time zone
Basically we are planning to have a single database server which will have 3 instances(belonging to different countries).
Ex: Singapore, India and Australia.
The server is going to be located in India.
Our application uses SYSDATE a lot. So whenever I give a query like 'select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual' I would like to see the country-specific time.
I did a bit of research on this and found out that,
1. Start OracleServiceIndia by setting the Time Zone to India.
2. Start OracleServiceAustralia by setting the Time Zone to Australia and the same method for Singapore.
This gives a impression that whenever the service is started it is picking up the time zone from the OS and based on that
it sets the time zone and the sysdate. If we follow this method then it works fine.
But if someone starts the service(without changing the time zone) then the entire application will go for a toss.
Can we specify the time zone while starting the service or is there any other work around for this.