03 November 2010

Change SYSDATE for testing

This morning I had some free time, so I was playing around with a little APEX 4 plugin. Probably the most simple plugin that you can imagine, but that is not what this post is about, or at least not mainly.The plugin shows the current date (or I should say: sysdate) as a region on an APEX page.Of course I needed to make sure that it works as SYSDATE changes. Of course I am not refering to the changing of the time component of SYSDATE.To change SYSDATE you can use this command. Note that it changes SYSDATE for the whole system.

SQL> alter system set fixed_date ='2011-08-05' 2 /

System altered.

Querying SYSDATE from SQL*Plus will show the effect:

SQL> select sysdate 2 from dual 3 /

SYSDATE---------05-AUG-11

Also note that SYSDATE is now fixed, not even the time component changes:

SQL> alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss' 2 /

Session altered.

SQL> select sysdate 2 from dual 3 /

SYSDATE-------------------05-08-2011 00:00:00

SQL> /

SYSDATE-------------------05-08-2011 00:00:00

Refreshing the APEX page also reflects the changed SYSDATE.

To restore the real SYSDATE, issue this command.

SQL> alter system set fixed_date = none 2 /

System altered.

SQL> select sysdate 2 from dual 3 /

SYSDATE-------------------03-11-2010 10:53:40

I knew about this functionality, but never thought I would actually need it.If you want the plugin, you can download it by following this link.

Any reason you didn't just change the date at the o/s level? I've never seen the point of the parameter myself. As you suggest it's useful in a test environment, but then in a test environment why not just lie to the o/s about the date?

No particular reason. Maybe because Outlook starts nagging me about all the deadlines that I missed when changing my date at the o/s.. But truth is I didn't think of that, SQL is always the first thing that comes to mind... :)

The downside of this parameter can be what you've already mentioned: that the time component doesn't change. For several applications depending on time deltas or scenarios where you want to see live what happens within a seconds' timeframe, using this parameter may not be an option for testing.

@Niall: On the other hand, travelling forth and back in time at the OS level can require a restart (i.e. downtime) while setting a parameter doesn't. Furthermore you might have to re-enable broken AWR data collection jobs when turning back time.

Thanks for that, I hadn't thought of the AWR jobs. As I've had cause to discover at 1am on Sunday as well if the the server has a Grid Agent on it (<10.2.0.5) and the clock goes *back* then the agent will die until the time has advanced past the last received upload. Yes there's a bug fix but I didn't download and apply it during DST. :(

Interesting. It will be good option to test those functionality that are time dependent. I will try to change the system date according to your commands. It will interesting to do and will try to play with the commands that you have shared above. Thank you.

About Me

Self-employed under the name allAPEX, mainly in The Netherlands. Presented at National and International Conferences. Oracle ACE Director for Database Development. Trainer for SQL and PL/SQL. Married, two children, likes to Barbecue.