Error Logs:
SQL> DROP profile old_or_inactive_sess_profile;
DROP profile old_or_inactive_sess_profile
*
ERROR at line 1:
ORA-02382: profile OLD_OR_INACTIVE_SESS_PROFILE has users assigned, cannot drop
without CASCADE
Solutions:
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 15 14:13:57 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> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 CNDBAPDB READ WRITE YES
6 CNDBAPDB3 MOUNTED
7 CNDBAPDB2 MOUNTED
8 CNDBAPDB4_FRESH MOUNTED
9 CNDBAPDB6 MOUNTED
SQL> alter session set container=PDB1;
Session altered.
SQL> create profile old_session_profile limit connect_time 120
2 /
Profile created.
SQL> -- kill sessions that have been inactive for 1 hour.
SQL> create profile inactive_session_profile limit idle_time 60
2 /
Profile created.
SQL> -- kill sessions older than 2 hours or inactive for 1 hour.
SQL> CREATE PROFILE old_or_inactive_sess_profile LIMIT
2 CONNECT_TIME 120
3 IDLE_TIME 60
4 /
Profile created.
SQL> -- Assign relevant profile to a user.
SQL> ALTER USER maxwellpan PROFILE old_or_inactive_sess_profile;
User altered.
SQL>
SQL>
SQL>
SQL> DROP profile old_or_inactive_sess_profile;
DROP profile old_or_inactive_sess_profile
*
ERROR at line 1:
ORA-02382: profile OLD_OR_INACTIVE_SESS_PROFILE has users assigned, cannot drop
without CASCADE
SQL> select profile from dba_users where username = 'maxwellpan';
no rows selected
SQL> show user;
USER is "SYS"
SQL> show con_name;
CON_NAME
------------------------------
PDB1
SQL> select profile from dba_users where username = 'MAXWELLPAN';
PROFILE
--------------------------------------------------------------------------------
OLD_OR_INACTIVE_SESS_PROFILE
SQL> Drop profile OLD_OR_INACTIVE_SESS_PROFILE cascade;
Profile dropped.
SQL> select profile from dba_users where username = 'MAXWELLPAN';
PROFILE
--------------------------------------------------------------------------------
DEFAULT
SQL>
This dropped the profile and assigned the DEFAULT profile to OLD_OR_INACTIVE_SESS_PROFILE instead: