Thursday, August 23, 2012

Oracle AS 10g : how to change schema account from EXPIRED(GRACE) to OPEN ?


Question :
Oracle AS 10g : how to change schema account from EXPIRED(GRACE) to OPEN ?

Answer :
1. find all users with account status equals EXPIRED(GRACE) 
  SELECT s.username,
        s.password
    FROM dba_users s
   WHERE s.account_status LIKE '%GRACE%'
     AND s.account_status NOT LIKE '%LOCKED%'

2. Using values from previous select statement, issue following commands for each user:

alter user username profile DEFAULT;
alter user username identified by values 'password'

BEGIN
  FOR u_rec IN (SELECT s.username,
                       s.password
                  FROM dba_users s
                 WHERE s.account_status LIKE '%GRACE%'
                   AND s.account_status NOT LIKE '%LOCKED%')
  LOOP
    EXECUTE immediate 'alter user '||u_rec.username||' profile DEFAULT';
    EXECUTE immediate 'alter user '||u_rec.username||' IDENTIFIED BY VALUES '''|| u_rec.password ||'''';
  END LOOP;
END;

No comments:

Post a Comment