Month: January 2017

So there’s this Oracle database instance in the midwest, in US/Central time zone.

So if I use PuTTY to open a terminal window on the host and open a SQL*Plus session, if I execute this statement I’ll get the following results:

SQL> select
       max( CHANGE_TS ) as MAX_CHANGE,
       max( CHANGE_TS at time zone 'US/Central' ) as MAX_CST_CHANGE
       from tRetired_Mst;
 
MAX_CHANGE                       MAX_CST_CHANGE
-------------------------------- ---------------------------------------------
05-JAN-17 02.33.17.613000 PM     05-JAN-17 02.33.17.613000 PM US/CENTRAL

As expected, they are the same, because the local time zone of the session (terminal window) is the same as the DB, US/Central.

However:

If I run PL/SQL Developer on my Windows Laptop here in California, and connect to the same Oracle instance, then the results are different:

SQL> select
       max( CHANGE_TS ) as MAX_CHANGE,
       max( CHANGE_TS at time zone 'US/Central' ) as MAX_CST_CHANGE
       from tRetired_Mst;
 
MAX_CHANGE                    MAX_CST_CHANGE
----------------------------- ----------------------------------------------
2017-01-05 14:33:17           05-JAN-17 04.33.17.613000 PM US/CENTRAL

Observe thatthe MAX_CST_CHANGE value is off by two hours! Because the timestamp value was evaluated as if it were in the local session time of my laptop, which is US/Pacific.

For code executing in PL/SQL packages in the database, this is probably not an issue… but if you are using this technique to convert values in ad-hoc SQL, you should be aware of this trap.

Note to self: Next time I want to shutdown an Oracle database, read this first:

• http://askdba.org/weblog/2008/05/shutdown-immediate-hang-2/

before issuing the SHUTDOWN IMMEDIATE command.

Right now, I am staring at

SQL> shutdown immediate

and I’m wishing I’d followed ANY of the suggestions in that blog post linked above.