CREATE USER

The CREATE USER statement creates a database account that allows you to log into the Oracle RDBMS.

The general format is:

CREATE USER username
IDENTIFIED {BY password | EXTERNALLY | GLOBALLY AS 'external_name'}
options;

options:
DEFAULT TABLESPACE tablespace
TEMPORARY TABLESPACE tablespace
QUOTA int {K | M} ON tablespace
QUOTA UNLIMITED ON tablespace
PROFILE profile_name
PASSWORD EXPIRE
ACCOUNT {LOCK|UNLOCK}

Example 1:

CREATE USER username IDENTIFIED BY secretpass;

Example 2:

DROP USER username CASCADE;
--create user
CREATE USER username IDENTIFIED BY secretpass
DEFAULT TABLESPACE data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON data;
--create role
CREATE ROLE programmer;
--grant programmer
GRANT CREATE session, CREATE table, CREATE view,
CREATE procedure,CREATE synonym,
ALTER table, ALTER view, ALTER procedure,ALTER synonym,
DROP table, DROP view, DROP procedure,DROP synonym,
TO programmer;
--grant user
GRANT programmer TO username;

Example 3:

CREATE USER globalusername
IDENTIFIED GLOBALLY AS 'CN=manager, OU=divisionname, O=companyname, C=US'
DEFAULT TABLESPACE data
QUOTA 200M on data;

Example 4:

CREATE USER externalusername
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE data
QUOTA 200M on data
PROFILE externaluserprofile;