How to fix ORA-02382 in Oracle Database

DB架构

已于 2023-02-15 18:09:10 修改

阅读量97

点赞数

分类专栏： Maxwell‘s Bug 文章标签：数据库 oracle linux

于 2023-02-15 14:34:43 首次发布

本文链接：https://blog.csdn.net/u011868279/article/details/129042924

版权

Maxwell‘s Bug 专栏收录该内容

39 篇文章 3 订阅

订阅专栏

在尝试删除Oracle数据库中的OLD_OR_INACTIVE_SESS_PROFILE时遇到错误ORA-02382，因为该Profile已分配给用户。通过查询DBA_USERS表确认用户MAXWELLPAN与此Profile关联。使用ALTERUSER命令将用户Profile更改为DEFAULT，然后使用CASCADE选项成功删除了OLD_OR_INACTIVE_SESS_PROFILE。

摘要由CSDN通过智能技术生成

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: