oracle函数返回值不变,sysdate返回值不变的问题处理

开发人员反映客户测试数据库环境取到的时间不对，经过多次查看后发现sysdate返回值一直不变

操作系统版本:

Linux CentOS 5.2

数据库版本

Oracle 11.2.0.2

问题描述和诊断过程

经过和客户沟通中得知客户DBA为了项目测试，需要把数据库的时间改掉，但是这个操作系统上面安装有多个实例，不能修改操作系统时间，所以使用下面方法修改了数据库的时间

ALTER SYSTEM SET

FIXED_DATE = 'DD-MON-YY';

但是客户不知道数据库没有自己的时间，也没有办法随便修改时间，设置这个值后sysdate返回值反而就变成了固定值

解决方案

解决方案比较简单，重置FIXED_DATE的值为NONE就可以了

ALTER SYSTEM SET FIXED_DATE=NONE;

总结

Sysdate只是简单调用了操作系统的时间函数(linux是调用"gettimeofday"方法)，Oracle数据库可以改变时区，也就是说数据库可以和操作系统的时区不一样(注意"TZ"环境变量要和数据库时区相同，不然通过listener连过来的时区会和数据不一致)，但是它没有自己的时间，数据库的时间没有办法通过参数或命令进行修改，如果需要修改时间只能修改操作系统的时间

关于时间和时区Oracle官方解释见metalink[ID 227334.1]

下面为[ID 227334.1]中提供的两个不同的数据库实例使用不同的时区的修改方法

How

do I set up 2 databases with a different SYSDATE / SYSTIMESTAMP time on the

same server?

Note: This is not possible on Microsoft Windows

systems ( from windows NT up to Windows 8) , as far as Oracle is aware

the timezone setting in Windows is "global" for all processes on the

machine and it is not possible to start on windows systems 2 processes who

recieve from the OS a time in a different timezone. For a final statement on

this please contact Microsoft.

The

SYSDATE function simply performs a system-call to the Operating System to get

the time (a call).  Therefore you can

not influence the sysdate values by changing database parameters.However, on Unix you can use the OS "TZ" environment

variable to alter the time that the OS will pass on to Oracle (also see above).

Therefore you can use this to influence the time that the OS passes to Oracle

as the "current time", and therefore you can set up multiple

databases on the same machine returning different times in the sysdate

function.

To do this you have 2 options:

1) When NOT using automatic listener

registration simply start a separate listener for each database, and set the TZ

appropriate for the database before you start the corresponding listener. It is

also a good idea to set the "correct" TZ in the Unix environment of

the user who (re)starts the database

If you use port 1521 for one of the listeners, then you should also disable

Automatic Service Registration by setting the following parameter to the

init.ora file:

LOCAL_LISTENER=dummyor , when using SPFILE, doSQL>ALTER SYSTEM SET LOCAL_LISTENER=dummy

SCOPE=SPFILE;

For more information on the background of this

please see:

Note:301420.1Why does sysdate have the Wrong Time Stamp

when Connecting via the ListenerNote:399448.1How To setup TNS listener to Show More

Than one Timezone

2) Alternatively if the only listener

registration is automatic listener registration then this can be used to have 2

databases with on listener and still have different timezones:

* start the listener on port 1521 WITHOUT any database configured in the

listener.ora.

* start each database with the TZ variable you want to use.

PMON will register the database with the

listener and the use TZ will be the one used to start the database, not

listener. If the listener is not running on port 1521 then you need to specify

in EACH database the LOCAL_LISTENER to enable automatic listener registration.

This is also usefull when using MTS.