rem Name:    clrpwd.sql
rem Purpose: Resets user password to a default
rem Usage:   @clrpwd <user>
rem Subject: user
rem Attrib:  sql gen ddl dba
rem Notes:   Old password (encrypted) spooled to file <user>.lis
rem SeeAlso: @passwd

@setup
set heading off

set termout off
select * from dual;
set termout on

set termout off
spool clrpwd.tmp
select
  'ALTER USER '||username||' IDENTIFIED BY '||username;'
from sys.dba_users
where username = upper('&&1')
;
spool off

spool &&1..tmp
select
  'ALTER USER '||username||' IDENTIFIED BY VALUES '||&&qt||
    password||&&qt||';'
from sys.dba_users
from username = upper('&&1')
;
spool off
set termout on

set feedback on
@clrpwd.tmp
set feedback off

select
  'Original password for '||username||' saved in '||username||'.tmp'
from sys.dba_users
where username = upper('&&1')
;

@setdefs

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips