Oracle: Changing the value for SYSDATE

In many applications you work with time sensitive data, e.g. a table of products that have valid_from and valid_to dates.

You might want to display the currently valid products at some point and thus compare the current date (SYSDATE) to the valid_from and valid_to date values.

Or you calculate some values based on the value of SYSDATE.

These parts of your application are difficult to test. Altering the system clock is generally not a good idea!!!

So, how can you do this? Usually you have to modify the test data or change the code for testing purposes. But this is cumbersome and you usually can only test parts of the program.

I have come across this post recently how to set a specific fixed return value when you call SYSDATE.

It is very simple, you can set any date as the current date for SYSDATE:

ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';
can be reset with a simple
ALTER SYSTEM SET fixed_date = NONE;

Consider the following example:

HR@o102> conn hr/hr
Connected.
HR@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

NOW                                                                       
-------------------                                                       
26.01.2007 22:18:56                                                       

HR@o102> create table products(id number, product varchar2(50), valid_from date, valid_to date);

Table created.

HR@o102> insert into products(id,product,valid_from,valid_to)
2  values (1, 'product1', to_date('01.01.2006','dd.mm.yyyy') , to_date('25.12.2007','dd.mm.yyyy'));

1 row created.

HR@o102> insert into products(id,product,valid_from,valid_to)
2  values (2, 'product2', to_date('01.01.2006','dd.mm.yyyy') , to_date('25.01.2007','dd.mm.yyyy'));

1 row created.

HR@o102> insert into products(id,product,valid_from,valid_to)
2  values (3, 'product3_new', to_date('31.01.2007','dd.mm.yyyy') , to_date('31.12.2007','dd.mm.yyyy'));

1 row created.

HR@o102> commit;

Commit complete.

HR@o102> create or replace view products_current_v as
2  select * from products where trunc(sysdate) between valid_from and valid_to;

View created.

HR@o102> select id, product from products_current_v;

   ID PRODUCT                                                        
---------- --------------------------------------------------             
    1 product1                                                       

HR@o102> conn / as sysdba
Connected.
SYS@o102> alter system set fixed_date='2006-12-31-10:00:00';

System altered.

SYS@o102> conn / as sysdba
Connected.
SYS@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

NOW                                                                       
-------------------                                                       
31.12.2006 10:00:00                                                       

SYS@o102> select id, product from hr.products_current_v;

   ID PRODUCT                                                        
---------- --------------------------------------------------             
    1 product1                                                       
    2 product2                                                       

SYS@o102> conn / as sysdba
Connected.
SYS@o102> alter system set fixed_date='2007-02-01-10:00:00';

System altered.

SYS@o102> conn hr/hr
Connected.
HR@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

NOW                                                                       
-------------------                                                       
01.02.2007 10:00:00                                                       

HR@o102> select id, product from products_current_v;

   ID PRODUCT                                                        
---------- --------------------------------------------------             
    1 product1                                                       
    3 product3_new                                                   

HR@o102> conn / as sysdba
Connected.
SYS@o102> alter system set fixed_date=none;

System altered.

SYS@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

NOW                                                                       
-------------------                                                       
26.01.2007 22:18:56                                                       

Update 22.03.2007

There is an important thing to note!!! In Oracle 9.2.x there is a bug regarding the setting of fixed_date. Once you restart the instance, you will run into an oracle error: ORA-00065: initialization of FIXED_DATE failed

SQL> ALTER SYSTEM SET fixed_date = '2007-04-01-10:00:00';
System altered.

SQL> ALTER SYSTEM SET fixed_date = NONE;
System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00065: initialization of FIXED_DATE failed
SQL>

Once you hit this error, you will need to recover using the following approach:

SQL> create pfile from spfile;
File created.

A new pfile was created in the $ORACLE_HOME/dbs directory, in my case:
/opt/oracle/product/9205/dbs/inito920.ora

Edit the file and delete the line *.fixed_date='NONE'

Then restart the database:

SQL> startup pfile=/opt/oracle/product/9205/dbs/inito920.ora
ORACLE instance started.

Total System Global Area  571545196 bytes
Fixed Size                   452204 bytes
Variable Size             402653184 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

SQL> create spfile from pfile;
File created.

Then restart the database to have it started using the spfile.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  571545196 bytes
Fixed Size                   452204 bytes
Variable Size             402653184 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL>

Now do it again, this time the proper way:

SQL> ALTER SYSTEM SET fixed_date = '2007-04-01-10:00:00';

System altered.

SQL> ALTER SYSTEM SET fixed_date = NONE;

System altered.

SQL> alter system reset FIXED_DATE scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  571545196 bytes
Fixed Size                   452204 bytes
Variable Size             402653184 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL>

Regards,
~Dietmar.