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