CREATE USER WITH TABLESPACE in the ORACLE

The syntax for the CREATE USER statement in Oracle/PLSQL is:

CREATE USER user_name 
  IDENTIFIED { BY password
             | EXTERNALLY [ AS 'certificate_DN' ]
             | GLOBALLY [ AS '[ directory_DN ]' ]
             }
  [ DEFAULT TABLESPACE tablespace
  | TEMPORARY TABLESPACE
       { tablespace | tablespace_group }
  | QUOTA integer [ K | M | G | T | P | E ]
        | UNLIMITED }
        ON tablespace
    [ QUOTA integer [ K | M | G | T | P | E ]
        | UNLIMITED }
            ON tablespace
    ]
  | PROFILE profile_name
  | PASSWORD EXPIRE
  | ACCOUNT { LOCK | UNLOCK }
     [ DEFAULT TABLESPACE tablespace
     | TEMPORARY TABLESPACE
         { tablespace | tablespace_group }
     | QUOTA integer [ K | M | G | T | P | E ]
           | UNLIMITED }
           ON tablespace
       [ QUOTA integer [ K | M | G | T | P | E ]
           | UNLIMITED }
           ON tablespace
        ]
     | PROFILE profile
     | PASSWORD EXPIRE
     | ACCOUNT { LOCK | UNLOCK } ]
     ] ;

PARAMETERS OR ARGUMENTS

user_name is the name of the database account that you wish to create.

PROFILE profile_name is optional. It is the name of the profile that you wish to assign to the user account to limit the amount of database resources assigned to the user account. If you omit this option, the DEFAULT profile is assigned to the user.

PASSWORD EXPIRE is optional. If this option is set, then the password must be reset before the user can log into the Oracle database.

ACCOUNT LOCK or UNLOCK is optional. ACCOUNT LOCK disables access to the user account. ACCOUNT UNLOCK enables access to the user account.

 

EXAMPLE

CREATE USER smithj
  IDENTIFIED BY pwd4smithj
  DEFAULT TABLESPACE tbs_perm_01
  TEMPORARY TABLESPACE tbs_temp_01
  QUOTA 20M on tbs_perm_01;

This CREATE USER statement would create a new user called smithj in the Oracle database whose password is pwd4smithj, the default tablespace would be tbs_perm_01 with a quota of 20MB, and the temporary tablespace would be tbs_temp_01.