Create User

This example was tested on an Oracle 9 database. When you create a user, you must either specify a password or make the user externally identified. Otherwise you will get an ORA-01938:
  

SQL> create user andrew;

create user andrew

                 *

ERROR at line 1:

ORA-01938: IDENTIFIED BY must be specified for CREATE USER

SQL> create user andrew identified by reid;

User created.

SQL> drop user andrew;

User dropped.

SQL> create user andrew identified externally;

User created.

SQL>

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>