Creating a User

To create a user in the database, you use the CREATE USER statement. The simplified syntax for the CREATE USER statement:

CREATE USER user_name IDENTIFIED BY password
[DEFAULT TABLESPACE default_tablespace]
[TEMPORARY TABLESPACE temporary_tablespace];

• user_name is the name of the database user.
• password is the password for the database user.
• default_tablespace is the default tablespace where database objects are stored. If you omit a default tablespace, the default SYSTEM tablespace is used.
• temporary_tablespace is the default tablespace where temporary objects are stored.

Tablespaces are used by the database to store separate objects. The following example connects as system and creates a user named tom with a password of Jerry:

CONNECT system/manager

SQL> CREATE USER tom IDENTIFIED BY Jerry;

User created.

SQL>

The following example creates a user named tom and specifies a default and temporary tablespace:

SQL> CREATE USER tom IDENTIFIED BY yourPassword
  2  DEFAULT TABLESPACE users
  3  TEMPORARY TABLESPACE temp;

User created.

SQL>

To view all table spaces

SELECT tablespace_name FROM dba_tablespaces.