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>

Reply to: Reply to Andrew Reid

Send

DBMS_STATS.SET_PARAM

The example below worked as shown in Oracle 10. First create a test table with an index:

 

SQL> create table andrews_table (col1 number)

  2  /

 

Table created.

 

SQL> create index andrews_index

  2  on andrews_table(col1)

  3  /

 

Index created.

SQL>

Next run dbms_stats.set_param and set cascade to false:

 

SQL> exec dbms_stats.set_param('cascade','false');

 

PL/SQL procedure successfully completed.

SQL>

Then, if you analyze the table using gather_table_stats:

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', tabname=>'andrews_table');

 

PL/SQL procedure successfully completed.

SQL>

The table is analyzed:

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_tables

  3  where table_name = 'ANDREWS_TABLE'

  4  /

 

ANALYZED

---------

15-MAY-12

SQL>

... but the index isn't:

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_indexes

  3  where index_name = 'ANDREWS_INDEX'

  4  /

 

ANALYZED

---------

NULL

SQL>

However, if you run dbms_stats.set_param and set cascade to true:

SQL> exec dbms_stats.set_param('cascade','true');

 

PL/SQL procedure successfully completed.

SQL>

When you analyze the table, the index is analyzed too:

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', tabname=>'andrews_table');

 

PL/SQL procedure successfully completed.

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_tables

  3  where table_name = 'ANDREWS_TABLE'

  4  /

 

ANALYZED

---------

15-MAY-12

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_indexes

  3  where index_name = 'ANDREWS_INDEX'

  4  /

 

ANALYZED

---------

15-MAY-12

 

SQL>

 

In Oracle 11, dbms_stats.set_param still runs but it has no effect. You can run

dbms_stats.set_param('cascade','false'); but the index might still be analyzed!

Drop User

This example was tested on an Oracle 10 database. You can remove a user from the database with the drop user command:

SQL> conn system/manager
Connected.
SQL> create user andrew identified by reid
  2  /

User created.

SQL> drop user andrew
  2  /

User dropped.

SQL>

But if the user owns any objects, you have to add the cascade option to the drop user command otherwise you will get an ORA-01922:

SQL> create user andrew identified by reid
  2  default tablespace users
  3  quota unlimited on users
  4  /

User created.

SQL> grant create session, create table to andrew
  2  /

Grant succeeded.

SQL> conn andrew/reid
Connected.
SQL> create table test_table (col1 number)
  2  /

Table created.

SQL> conn system/manager
Connected.
SQL> drop user andrew
  2  /
drop user andrew
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'ANDREW'

SQL> drop user andrew cascade
  2  /

User dropped.

SQL>