0
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> ALTER USER DBSNMP account UNLOCK;
ALTER USER DBSNMP account UNLOCK
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> SHOW parameter pluggable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_pluggable_database            boolean     TRUE
SQL> col username FOR a20
SQL> SELECT username,account_status,password_versions,ORACLE_MAINTAINED FROM dba_users WHERE username='DBSNMP';

USERNAME             ACCOUNT_STATUS                   PASSWORD_VER O
-------------------- -------------------------------- ------------ -
DBSNMP               EXPIRED & LOCKED                 10G 11G 12C  Y

SQL> conn / AS sysdba
Connected.
SQL> ALTER USER DBSNMP account UNLOCK;
ALTER USER DBSNMP account UNLOCK
*
ERROR at line 1:
ORA-01031: insufficient privileges
Vno
  • 21
  • 1
  • 5
  • Looks like you need to un-expire then unlock. Check [this](http://riaschissl.bestsolution.at/2010/07/oracle-unexpire-and-unlock-accounts/) – bprasanna Nov 25 '16 at 03:40
  • Yes, I tried unexpire before unlock. Even that throws same error.SQL> alter user dbsnmp identified by dbsnmp_user_1; alter user dbsnmp identified by dbsnmp_user_1 * ERROR at line 1: ORA-01031: insufficient privileges – Vno Nov 25 '16 at 05:44
  • This is data vault enabled environment. – Vno Nov 25 '16 at 05:45
  • Then you need to do the user operations using an user who has `DV_ACCTMGR` role. Check [this](https://docs.oracle.com/cd/B28359_01/server.111/b31222/getting_started.htm#DVADM70118) sample. – bprasanna Nov 25 '16 at 06:02
  • Thank you. I connected as DV_ACCTMGR and unlocked the dbsnmp. – Vno Nov 25 '16 at 07:13
  • @Vno How did you do that? Did you log in with some user with DV_ACCTMGR role? what is that user ? I connected as SYS and I get this result. DV_ACCTMGR SYS DBA_PROFILES SYS READ NO NO YES VIEW NO DV_ACCTMGR SYS DBA_USERS SYS READ NO NO YES VIEW NO Not sure of I can use this account to unlock DBSNMP – Krish Sanj Nov 01 '20 at 06:45

1 Answers1

0

When dealing with a pluggable database you have to alter your session to tell Oracle what container you are targeting. Use the cmd (show pdbs;) to see the containers and then an (alter session) to set the container. Like:

[oracle@ora_oemdb ~]$ . oraenv
ORACLE_SID = [prodoem] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora_oemdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 14 16:19:41 2023
Version 19.3.0.0.0

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


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

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
prodoem

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBPDEM                        READ WRITE NO
         
SQL> alter session set container=PDBPDEM;

Session altered.

SQL> ALTER USER DBSNMP account UNLOCK;

User altered.

SQL>
jmichaelwDBA
  • 19
  • 5