Time travel with fixed_date

My Dad is a bit of a clever-clogs at the moment.
When he confidently predicted that Chelsea would beat Barcelona in the Champions League back in April,
I put this down to an affinity with the Chelsea squad which, lets face it, is rather nearer to his age-group than those at most other big clubs.
Since then however, he’s been on a roll, culminating in his unerring prediction that Sweden would beat France in the Euros.
This almost cephalopodic accuracy ( remember Paul the Octopus) is about to be put to it’s sternest test.
According to Dad, England will beat Italy on Sunday.
Hopefully, this is just another example of his prescience rather than a case of being swept up in the mood of the moment. Roy Hodgson has gone from being “why on did they go for this old codger” to “I always knew he was the man for the job” in the space of six weeks.
English football fans, fickle? I don’t know where you get that idea.
All of which provides a fairly tenuous link to the subject at hand – testing date sensitive changes in Oracle.

The Test Environment

There’s your test server, wheezing away in some forgotten corner of the server room, running a number of test databases.
Traditionally, if you wanted to test something date-sensitive, you’d have to reset the server date to the time and date in question, which would affect all of the Oracle instances running on that server.
If only there was a way of setting this at a database level.
Well, there is.

The fixed_date parameter

The fixed_date parameter has been around for several years ( since at least 8i), but remains surprisingly obscure.

By setting this to the required value, you can ensure that your database will use this date rather than the current server date and time.

Setting the parameter

You need to connect to the database as a user with ALTER SYSTEM privileges. If you need to check :

SQL> SELECT *
  2  FROM session_privs
  3  WHERE privilege = 'ALTER SYSTEM';

PRIVILEGE
----------------------------------------
ALTER SYSTEM

SQL>

If you get a row back then you’re good to go…

SQL> SELECT SYSDATE FROM dual;

SYSDATE
---------
22-JUN-12

SQL>

Now, to go back in time….

SQL> ALTER SYSTEM SET fixed_date='01-JUN-2012';

System altered.

SQL> SELECT SYSDATE FROM dual;

SYSDATE
---------
01-JUN-12

SQL>

What time is now ?

The date we entered is truncated, as you’d expect, so the actual time is midnight.
If you want to set a specific time (down to the second)…

SQL> ALTER SYSTEM SET fixed_date = '2012-06-01-09:00:00';

System altered.

SQL> SELECT TO_CHAR(SYSDATE, 'HH24:MI') FROM dual;

TO_CH
-----
09:00

SQL>

Notice here that we need to use the ISO date format (YYYY-MM-DD-HH24:MI:SS).

Getting back to the present

Re-setting the date should be fairly straightforward shouldn’t it ?

SQL> ALTER SYSTEM SET fixed_date = NULL;
ALTER SYSTEM SET fixed_date = NULL
       *
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02078: invalid setting for ALTER SYSTEM FIXED_DATE


SQL>

Hmmm…maybe not. What now ? I have come across instances where people have refreshed the database at this point,
just to clear the fixed_date setting. However, there is a simpler solution…

SQL> ALTER SYSTEM SET fixed_date = NONE;

System altered.

SQL> SELECT sysdate from dual;

SYSDATE
---------
22-JUN-12

SQL>

A word of warning

Fixed date works perfectly as far as SYSDATE is concerned. However, if you’re using SYSTIMESTAMP…

SQL> alter system set fixed_date='01-JUN-2012';

System altered.

SQL> select sysdate from dual;

SYSDATE
---------
01-JUN-12

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
22-JUN-12 22.39.19.773000 +01:00

SQL>

If you’re now experiencing a sudden pang of disappointment at discovering that fixed_date wasn’t quite what you were hoping for when you started reading this, then I can only empathise.
You are now experiencing what it feels like to follow England at a major football tournament !

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s