Creating New User in Oracle 9i

example:

CREATE USER user1
IDENTIFIED BY pass
DEFAULT TABLESPACE mytab
TEMPORARY TABLESPACE temp1
QUOTA 10M ON mytab
QUOTA 5M ON temp1
PASSWORD EXPIRE;

where:

user1: name of the user
BY pass:pass is set as password
DEFAULT TABLESPACE:set the tablespace where segment that are created by the user are stored
QUOTA: Defines the max space allowed for objects owned by the user in the tablespace.
PASSWORD EXPIRE : Forces the user to reset the password when the user logs on to the database.

Changing User Quota

ALTER USER user1
QUOTA 0 ON mytab;

Dropping a User

DROP USER user1

Use the CASCADE clause to drop all objects in the chema if the schema contains objects.

DROP USER user1 CASCADE

User Information

to obtain user information query following views

A) DBA_USERS
B) DBA_TS_QUOTAS