여러가지 이유로 계정이 Expired(Grace) 상태가 되었다면, 암호를 재설정하지 않는 한 유저를 open 상태로 되돌릴 수 없습니다. 기존의 암호를 알 수 없고 변경도 불가능한 상황이라면 SYS.USER$ 테이블의 PASSWORD, SPARE4 컬럼을 사용하여 기존 패스워드 유지하며 OPEN 상태로 변경 가능합니다. 

    현재 패스워드 백업

     
[ora19ent@svr ~]$ sqlplus / as sysdba
-- usr1 계정 패스워드 설정
SQL> alter user usr1 identified by "usr1" ;
User altered.

-- 접속 테스트
SQL> conn usr1/usr1
Connected.

[ora19ent@svr ~]$ sqlplus / as sysdba
-- 계정 정보 확인
SQL> SELECT NAME,PASSWORD, SPARE4
FROM SYS.USER$
WHERE NAME = 'USR1'
;

NAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
USR1

S:E63E217D3CD06E837DF8CBF3965E1FF375E431F83C401A19963FD4F53B20;T:30D62CDBD72B977
30E1BDB0A93B87A16632F639E065CBEE9CB5F387C875008BC6DF93FC4386372E0E31A5BC11FAA1FE
C917C32E93ACB6D0E0B33F2BC9AE26D6B0EC363D838000D2A5A0A9C578BA089B6


-- 현재 패스워드 백업, 나중에 적용할 ALTER 구문 생성 해줌
SQL> SELECT 'ALTER USER '||NAME||' IDENTIFIED BY VALUES '''||SPARE4||';'||PASSWORD||''';' AS QUERY
FROM SYS.USER$
WHERE name = 'USR1'
;

QUERY
--------------------------------------------------------------------------------
ALTER USER USR1 IDENTIFIED BY VALUES 'S:E63E217D3CD06E837DF8CBF3965E1FF375E431F8
3C401A19963FD4F53B20;T:30D62CDBD72B97730E1BDB0A93B87A16632F639E065CBEE9CB5F387C8
75008BC6DF93FC4386372E0E31A5BC11FAA1FEC917C32E93ACB6D0E0B33F2BC9AE26D6B0EC363D83
8000D2A5A0A9C578BA089B6;';

    패스워드 변경 후 접속 테스트

     
-- usr1 패스워드 변경
SQL> alter user usr1 identified by "usr111" ;

User altered.

-- 변경된 정보 확인
SQL> SELECT NAME,PASSWORD, SPARE4
FROM SYS.USER$
WHERE NAME = 'USR1'
;

NAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
USR1

S:8B0D23D60B50E379375D795417D9D80B578AA5404E813FAD791A2A989A5E;T:B47B3AB8459657D
D8A380DD2CB92404AF62F9A261B1626B6B6958990A8E6600B8EE145254E80AD822A91E5A59438A1B
90DB6ABF37AE6BFBB30F5BD9DC5D8958D8DE86155B88673938FD3BA4924FD98E5


-- 변경전 패스워드 접속 실패
SQL> conn usr1/usr1
ERROR:
ORA-01017: invalid username/password; logon denied

-- 변경된 패스워드 접속 성공
Warning: You are no longer connected to ORACLE.
SQL> conn usr1/usr111
Connected.

    기존 패스워드로 복구

     
[ora19ent@svr ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 16 03:58:35 2024
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

-- 백업해둔 기존 패스워드로 변경
SQL> ALTER USER USR1 IDENTIFIED BY VALUES 'S:E63E217D3CD06E837DF8CBF3965E1FF375E431F83C401A19963FD4F53B20;T:30D62CDBD72B97730E1BDB0A93B87A16632F639E065CBEE9CB5F387C875008BC6DF93FC4386372E0E31A5BC11FAA1FEC917C32E93ACB6D0E0B33F2BC9AE26D6B0EC363D838000D2A5A0A9C578BA089B6;';

User altered.

-- 변경 확인
SQL> SELECT NAME,PASSWORD, SPARE4
FROM SYS.USER$
WHERE NAME = 'USR1'
;

NAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
USR1

S:E63E217D3CD06E837DF8CBF3965E1FF375E431F83C401A19963FD4F53B20;T:30D62CDBD72B977
30E1BDB0A93B87A16632F639E065CBEE9CB5F387C875008BC6DF93FC4386372E0E31A5BC11FAA1FE
C917C32E93ACB6D0E0B33F2BC9AE26D6B0EC363D838000D2A5A0A9C578BA089B6

-- 변경전 패스워드 접속 성공
SQL> conn usr1/usr1
Connected.
SQL>