Sunday, October 14, 2012

ORA-02382

This was tested on Oracle 11.2. I wanted to see if you could drop a profile if somebody was using it. First I created a profile.

SQL> create profile andrews_profile
  2  limit failed_login_attempts 3
  3  /

Profile created. 

SQL> 

Then I assigned the profile to a user:

SQL> create user andrew identified by reid
  2  profile andrews_profile
  3  /

User created.

SQL> select profile from dba_users
  2  where username = 'ANDREW'
  3  /

PROFILE
------------------------------
ANDREWS_PROFILE 

SQL> 

I tried to drop the profile but this failed as it was in use:

SQL> drop profile andrews_profile
  2  /
drop profile andrews_profile
*
ERROR at line 1:
ORA-02382: profile ANDREWS_PROFILE has users assigned,
cannot drop without CASCADE

SQL>

I ran the command again, adding CASCADE at the end:

SQL> drop profile andrews_profile cascade
  2  /

Profile dropped.

SQL>

This dropped the profile and assigned the DEFAULT profile to ANDREW instead:

SQL> select profile from dba_users
  2  where username = 'ANDREW'
  3  /

PROFILE
------------------------------
DEFAULT

SQL>

No comments: