asked on

unexpire oracle account

12:15:37 SQL> select username,user_id,account_status,password,PROFILE from dba_users;

USERNAME USER_ID ACCOUNT_STATUS PASSWORD
------------------------------ ---------- -------------------------------- ------------------------------
PROFILE
------------------------------
OPS$SAPSERVICEZPX 37 OPEN EXTERNAL
DEFAULT

OPS$ORAZPX 39 OPEN EXTERNAL
DEFAULT

OPS$ZPXADM 33 OPEN EXTERNAL
DEFAULT

SYS 0 OPEN
DEFAULT

SYSTEM 5 OPEN
DEFAULT

SAPSR3DB 38 EXPIRED
SAPUPROF

SAPSR3 34 EXPIRED(GRACE)
SAPUPROF

OUTLN 9 LOCKED
DEFAULT

APPQOSSYS 31 EXPIRED & LOCKED
DEFAULT

DIP 14 EXPIRED & LOCKED
DEFAULT

DBSNMP 30 EXPIRED & LOCKED
DEFAULT

ORACLE_OCM 21 EXPIRED & LOCKED

Need to unexpire account for user SAPSR3DB VERY URGENTLY PRODUCTION SYSTEM AFFECTED oracle 11g

Naveen Kumar

reset the password for them which are showing as EXPIRED ....

reset the password and unlock the account which are showing as EXPIRED & LOCKED

Muhammad Ahmad Imran

UNLOCK COMMAND WILL DO THAT

SOLUTION

Christoffer Swanström

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

OP_Zaharin

- as SAPSR3DB is marked as expired, you just need to set a new password for it:
ALTER USER SAPSR3DB IDENTIFIED BY <newpassword>;

profjohan11

ASKER

hi,

This is what worked for me:

assword set to original password fpr user sapsr3db

SQL> conn sapsr3db
Enter password:
ERROR:
ORA-28001: the password has expired

Changing password for sapsr3db
New password:
Retype new password:
Password changed
Connected.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

My question is as follows:

What is the difference between my solution and using ALTER USER SAPSR3DB IDENTIFIED BY <newpassword>; ?

I do know that this is a SAP instance running on oracle so if you only do this and set new password you may have inconsistencies in sap due toe sapuser table not being updated.

ALTER USER SAPSR3DB IDENTIFIED BY <newpassword>; ?

Another sap suggessted solution was to use brconnect -u sapsr3db/\password -f chpass and this did not work either.

I will award points shortly to all who answers and thanks for your replies.

Johan

Christoffer Swanström

You say you set the password to the original one. Where did you do that?

ASKER CERTIFIED SOLUTION

OP_Zaharin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Wasim Akram Shaik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jayesh Acharya

-- get you r account uloncked and set password to Sap1234!

alter user SAPSR3DB account unlock;

alter user SAPSR3DB identified by Sap1234!;

SOLUTION

Jayesh Acharya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

profjohan11

ASKER

H guys,

How do I determine the password_grace_period before account locks.

i.e. username sapsr3 and profile name sapuprof

Christoffer Swanström

SELECT * FROM dba_profiles WHERE resource_name = 'PASSWORD_GRACE_TIME'

Open in new window

Christoffer Swanström

To be more accurate:

SELECT * FROM dba_profiles WHERE resource_name = 'PASSWORD_GRACE_TIME' AND profile = 'SAPUPROF'

Open in new window

Wasim Akram Shaik

this query will give you those details..

you need to query dba_users to get the profile_name of an user an that should be substituted in below query to get the desired result

select * from dba_profiles where profile='<PROFILE_NAME>' and name='PASSWORD_GRACE_TIME';

SOLUTION

Christoffer Swanström

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

profjohan11

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Christoffer Swanström

Check expiry_date from dba_users and then add the password_grace_time from dba_profiles.

Christoffer Swanström

Here's a query to do that, just add the proper user name:

SELECT
usr.username
,usr.expiry_date
,prf.limit
,usr.expiry_date + prf.limit AS grace_time_limit
FROM
dba_users usr
INNER JOIN
dba_profiles prf
ON
prf.profile = usr.profile
AND
prf.resource_name = 'PASSWORD_GRACE_TIME'
WHERE
usr.username = <ADD USER NAME>

Christoffer Swanström

By the way, I think your original question about "unexpiring" accounts has been answered. For any follow-up questions it would be fair to open a new question.