Monday 28 January 2013

Solving ORA-28001 Without Changing the Password

Unexpectedly, several months after upgrading to Oracle 11g, our SYSMAN and DBSNMP account passwords expired, creating lots of errors in our audit logs. We never had this with 10g, they never expired.

Not wanting to dive into lots of unfamilar config files, we looked at unexpiring the accounts, keeping the existing passwords.

A little googling and tweaking later, the following SQL command outputs alter user statements to "unexpire" the accounts.

I'm not sure if this method is supported by Oracle or not but it worked for us.

SELECT 'ALTER USER '||u.name||' IDENTIFIED BY VALUES '''||u.spare4||';'||u.password||''';' cmd FROM sys.user$ u WHERE u.name IN ('SYSMAN','DBSNMP') ;
User altered