Oracle unexpire password

Imposta per evitare la scadenza

create profile nopasswdexpire_profile limit PASSWORD_LIFE_TIME unlimited FAILED_LOGIN_ATTEMPTS unlimited;

begin
for c1 in (select username from dba_users where profile='DEFAULT')
loop
  begin
   execute immediate 'alter user ' || c1.username || ' profile nopasswdexpire_profile';
   exception
    when others then
	  null;
  end;
end loop;
end;
/

Password Unexpire

(Bisogna scrivere un unica procedura che gestisca tutti e tre i casi)

begin
 for c1 in (select t1.username u,t2.password p,t2.spare4 s from dba_users t1, sys.user$ t2 where t2.name=t1.username and t1.account_status in ('EXPIRED','EXPIRED(GRACE)') and t1.password_version='10G 11G')
 loop
  execute immediate 'alter user ' || c1.u || ' idenitified by values ''' || c1.s || ';' || c1.p || ''';
 end loop;
end;
/

begin
 for c1 in (select t1.username u,t2.password p,t2.spare4 s from dba_users t1, sys.user$ t2 where t2.name=t1.username and t1.account_status in ('EXPIRED','EXPIRED(GRACE)') and t1.password_version='11G')
 loop
  execute immediate 'alter user ' || c1.u || ' idenitified by values ''' || c1.s || ''';
 end loop;
end;
/

begin
 for c1 in (select t1.username u,t2.password p,t2.spare4 s from dba_users t1, sys.user$ t2 where t2.name=t1.username and t1.account_status in ('EXPIRED','EXPIRED(GRACE)') and t1.password_version='10G')
 loop
  execute immediate 'alter user ' || c1.u || ' idenitified by values ''' || c1.p || ''';
 end loop;
end;
/