How to re-open expired oracle database account without change password

Today i’ll show you how to reopen Oracle database user account without changing password, which have status expired. let’s create demonstration:
Here is video of these procedures
create user

CREATE USER test IDENTIFIED BY test;
grant create session to test;

check status

SELECT username, account_status, expiry_date
 FROM dba_users
 WHERE username = 'TEST';
-------------------------------------------	
USERNAME ACCOUNT_STATUS	EXPIRY_DATE
TEST	 OPEN	        20-Sep-14 11:29:00

expire it

alter user test password expire;

re-check status

SELECT username, account_status, expiry_date
 FROM dba_users
 WHERE username = 'TEST';
-------------------------------------------
USERNAME ACCOUNT_STATUS	EXPIRY_DATE
TEST	 EXPIRED	24-Mar-14 11:31:50

get the existing password, here is two method:
1) in 11g

select password from user$ where name='TEST';
PASSWORD
------------------------------
7A0F2B316C212D67

in 10g

select password from dba_users where username='TEST';
PASSWORD
------------------------------
7A0F2B316C212D67

open account with

alter user test identified by values '7A0F2B316C212D67';

2)

SELECT DBMS_METADATA.get_ddl ('USER', 'TEST')
FROM DUAL;
-------------------------------------------
   CREATE USER "TEST" IDENTIFIED BY VALUES 'S:79B1417837DCF0FBFACEFB10D7DBDC7B7EA63CC986036567BDCBA144B940;7A0F2B316C212D67'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE

edit above script and execute it

ALTER USER "TEST" IDENTIFIED BY VALUES 'S:79B1417837DCF0FBFACEFB10D7DBDC7B7EA63CC986036567BDCBA144B940;7A0F2B316C212D67'

chek status again

SELECT username, account_status, expiry_date
 FROM dba_users
 WHERE username = 'TEST';
-------------------------------------------
USERNAME ACCOUNT_STATUS	EXPIRY_DATE
TEST	 OPEN	        20-Sep-14 11:36:47

after that user test still have its old pass test.
that’s all, good luck.

One thought on “How to re-open expired oracle database account without change password

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s