Saturday, June 18, 2011

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>

No comments: