Error
SQL> alter user dvf account unlock;
alter user dvf account unlock
*
ERROR at line 1:
ORA-65146: account cannot be unlocked in a PDB while it is locked in the root
Cause
Error occurred due to tried to unlock the common user from PDB database which is locked by the Container Database.
Solution
You need to login to container database with sysdba privileges to unlock the account.
Example show you the process of lock and unlock the DVF user which is common user present in Database for PDBs and ROOT container.
-- Login with Container ROOT as sysdba and locked the DVF account
SQL> alter user dvf account lock;
User altered.
SQL> col username for a15
SQL> col account_status for a15
SQL> select username,account_status,common from dba_users where username = 'DVF';
USERNAME ACCOUNT_STATUS COM --------------- --------------- --- DVF LOCKED YES
--Login at PDBs databaes and check status of DVF user
SQL> conn test@xepdb1
Enter password:
Connected.
SQL> select username,account_status,common from dba_users where username = 'DVF';
USERNAME ACCOUNT_STATUS COM --------------- --------------- --- DVF LOCKED YES
-- Tried to unlock the common user from PDB login
SQL> alter user dvf account unlock;
alter user dvf account unlock
*
ERROR at line 1:
ORA-65146: account cannot be unlocked in a PDB while it is locked in the root
-- Login to sysdba of ROOT container for unlock the DVF
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user dvf account unlock;
User altered.
SQL> select username,account_status from dba_users where username = 'DVF';
USERNAME ACCOUNT_STATUS --------------- --------------- DVF OPEN
--Check status from Pdbs container database
SQL> conn test@xepdb1
Enter password:
Connected.
SQL> select username,account_status from dba_users where username = 'DVF';
USERNAME ACCOUNT_STATUS --------------- --------------- DVF OPEN
-- Lock the DVF from PDB database
SQL> alter user dvf account lock;
User altered.
SQL> select username,account_status from dba_users where username = 'DVF';
USERNAME ACCOUNT_STATUS --------------- --------------- DVF LOCKED
--But ROOT container DVF is in open status
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username,account_status from dba_users where username = 'DVF';
USERNAME ACCOUNT_STATUS --------------- --------------- DVF OPEN
*
Smart way of Technology 