Changing SYSDATE to be static or dynamic

Sometimes I’ll work on a database where SYSDATE is not entirely accurate. Sometimes the server is in another time zone or it hasn’t been set to accurately reflect what you would expect. It’s no big deal it’s just something you notice. However I worked at a client who had altered the date in their Test environments in a way I wasn’t even aware was possible. It’s possible to alter SYSDATE in an Oracle Database to be a fixed date and time.

To make sysdate static

To remove the fixed date and allow sysdate to return the server date and time again clear the fixed_date

You would notice pretty quickly if this had been done in your database because all your audit columns would have the same creation and modification dates and times.

Below I wrote some code to output the current sysdate, then add a random number of hours to sysdate and output the new sysdate.

But what you can also do is create a database job to increment the date at regular intervals by a random number of hours. Now this would cause some serious confusion.

This would also be fairly easy to spot because the database time is effectively standing still for a minute at a time. At the client site that I was working at for a reason that I still do not know the time was only standing still for a second at a time and looked like a fully formed time with milliseconds that weren’t rounded to make spotting the issue easier.

Kylie Payne
Dancing With Oracle