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>