Setting SYSDATE to a fixed value

Dietmar Aust just blogged about a very interesting trick to modify SYSDATE which I didn’t know so far.

As he writes in his posting this can be very useful for testing if you have to do some time traveling…

In our company we used a different approach by writing a setSYSDATE and getSYSDATE method which has to be used instead of SYSDATE. But the trick to just set

ALTER SYSTEM SET fixed_date = '2007-01-27-11:50:00';

is much easier and also works if a developer has used SYSDATE. For details and what other commands you have to issue, check out Dietmar’s posting.

6 thoughts on “Setting SYSDATE to a fixed value

  1. Just remember that this is an ALTER SYSTEM command, not an ALTER SESSION…

    We have mulitple development workspaces on a single database instance. These workspaces are supposed to be utterly independant and consequently this technique is of no use to us for running tests in our dev spaces.

    Bear that in mind…

  2. Rob,

    you are absolutely right! I also noticed that when I took a closer look at the documentation today.

    Was too good to be true. Seems that I have to keep our packages.

    But it’s maybe still useful for other people.


  3. It doesn’t rule out its use completely though… we did find a great use for it on our performance test rig.

    In order to ensure that the only thing changing between performance test runs was the thing that we meant to change (SGA, Block Size, whatever), we set up a rig that would always perform the same queries on the same order, at the same time, against the same data… then fixed the sysdate before the run started.

    In that situation we DID want everyone who connected to that daabase to see the same time every time.

    Mind you, if we owned the source code for the app we’d probably have wrapped all the SYSDATE calls much like you’ve done… 😉

  4. I don’t see any sense in setting SYSDATE to a fixed value if it isn’t a Session command.

    To change SYSDATE Systemwide it would be enough to change the database date. Same effect.

  5. Hi Rag,

    I would still see that this command is useful.
    -) If the DB node has some time sync processes set up, you have to disable them, … before you are able to change the time, you have to enable them later on again, much more work then just issuing this command
    -) There is a big difference in setting the time to a fixed value and just changing the database time.

    The database time will continue to increase and you have less control which timestamp is finally used for your test, with the fixed date you have full control and you decide when the time should change -> eg. when you are done with your first test and when you want to proceed with the next test.


  6. Hi Patrick,

    i can see your point, but for me it is useless. I can’t think of any situation (in my environment) where i want to set SYSDATE to a fixed date (which is not increasing as time goes by) for the whole system.

Comments are closed.