Archive

Archive for October, 2012

The missing hour

Yesterday I wrote a validation program for files with measurements. It reported 2012-03-25 02:00 as an invalid date given the date format yyyy-MM-dd HH:mm. Strange, what could be wrong?

Eventually I found it. This blows up:


DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
df.setLenient(false);
df.parse("2012-03-25 02:00");

However, change the time zone to UTC and it works:


df.setTimeZone(TimeZone.getTimeZone("UTC"));

Daylight savings (again). The specified hour does not exist with the default time zone (Europe/Stockholm).

Categories: Java

Fix unknown expired Oracle passwords without changing them

The way Oracle handles password expiration can be quite frustrating at times. To enable an expired user the DBA must basically set the password. Locked users can be unlocked with a simple command, but there is no way to unexpire an expired password. If the password expired by mistake and is used by a system rather than a user, changing it may be a big deal. You need to get things up and running and you need to do it fast. So, what to do?

Fortunately it is possible to set the password to the same value it had before using identified by values and the hashed password. Oracle does not show the hashed password in dba_users, but it can be retrieved.

This code finds specified users, unlocks their accounts and resets the passwords:


begin
  for rec in (select
    regexp_substr( dbms_metadata.get_ddl('USER', username), '''[^'']+''') pw,
    username from dba_users where username IN ('SYSMAN')) loop
    execute immediate 'alter user ' || rec.username || ' account unlock';
    execute immediate 'alter user ' || rec.username || 
      ' identified by values ' || rec.pw;
  end loop;
end;
/

Simply replace the user(s) in the query (SYSMAN in this case) and run the script as a DBA. Business as usual again!

Categories: Oracle