Creating database users in Oracle

A newly created user cannot connect to the database until granted the CREATE SESSION system privilege. Usually, a newly created user is granted a role similar to the predefined roll CONNECT (used in this example) that specifies the CREATE SESSION and other basic privileges required to access a database.

create user user1 identified by pwd1 DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;


GRANT CREATE SESSION TO user1;

In order to check whether the user has been created, run

select username from dba_users;

and to check if you have the correct name for the tablespace, check dba_tablespaces table:

select tablespace_name from dba_tablespaces;