1、查看用户的proifle是那个，一般是default：

sql>SELECT username,PROFILE FROM dba_users;

2、查看指定概要文件（如default）的密码有效期设置：

sql>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

3、将密码有效期由默认的180天修改成“无限制”：

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;


如果使用不同的配置文件名称作为 DEFAULT 或多个配置文件,需调整语句;
使用相同的语句完成更改的验证:
SELECT * FROM dba_profiles s WHERE resource_name='PASSWORD_LIFE_TIME';


4.检查有问题的账户:

概述数据库中是否显示任何已过期或已锁定的账户:


select username,account_status from dba_users where account_status like 'EXPIRED' or 
account_status like '%LOCKED%'

使用脚本重新打开受影响的账户的两种方式,
主要区别是方式1将所有过期账户设置为脚本中给出的新密码;方式2执行相同操作,但将账户密码重置为旧密码.

1) 给所有用户重新设置新密码,创建一个名为 userpwd.sql 的sql文件

spool on;
set echo off;
set heading off;
set feedback off;
SET SERVEROUTPUT OFF;
spool unlock.sql;
select 'ALTER USER'  || USERNAME || 'account unlock;' from dba_users where  ACCOUNT_STATUS like '%LOCKED%';
spool off;
@unlock.sql;
spool on;
set echo off;
set heading off;
set feedback off;
SET SERVEROUTPUT OFF;
spool pwchangen.sql;
select 'ALTER USER'  || USERNAME || 'identified by '需要修改的密码';'  from dba_users where  
ACCOUNT_STATUS like 'EXPIRED' or 
ACCOUNT_STATUS like '%LOCKED%';
spool off;
@pwchangen.sql;



2) 恢复以前的密码,创建名为 userpwd.sql 的sql文件

spool on;
set echo off;
set heading off;
set feedback off;
SET SERVEROUTPUT OFF;
spool unlock.sql;
select 'ALTER USER'  || USERNAME || 'account unlock;' from dba_users where  ACCOUNT_STATUS like '%LOCKED%';
spool off;
@unlock.sql;
spool on;
set lines 300;
set echo off;
set heading off;
set feedback off;
SET SERVEROUTPUT OFF;
spool pwchangeo.sql;
select 'ALTER USER'  || USERNAME || 'identified by  values ''' || spare4 ||  ''';'  from dba_users,user$ where  
ACCOUNT_STATUS like 'EXPIRED' and USERNAME=NAME;
spool off;
@pwchangeo.sql;

5.使用脚本后

给用户解锁:

ALTER USER OHS  cccount unlock;

修改用户密码:

ALTER USER OHS identified by welcomme1;

修改后不需要重启数据库,立即生效