Oracle/Create tablespace

Create a new tablespace:

CREATE TABLESPACE TEST LOGGING 
  DATAFILE '/usr/lib/oracle/xe/oradata/XE/test.dbf' 
  SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED 
  EXTENT MANAGEMENT LOCAL;

Create a new temporary tablespace:

CREATE TEMPORARY TABLESPACE TEST_TEMP
  TEMPFILE '/usr/lib/oracle/xe/oradata/XE/test_temp.dbf'
  SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED 
  EXTENT MANAGEMENT LOCAL;

Create a new user for this table space with appropriate rights (important: case must be the same for both, user name and table space!):

CREATE USER "TEST" PROFILE "DEFAULT" 
  IDENTIFIED BY "PASSWORD"
  DEFAULT TABLESPACE "TEST"
  TEMPORARY TABLESPACE "TEST_TEMP"
  ACCOUNT UNLOCK;

-- ROLE RESOURCE: CREATE CLUSTER, INDEXTYPE, OPERATOR, PROCEDURE, SEQUENCE, TABLE, TRIGGER, TYPE
-- ROLE CONNECT: CREATE SESSION
GRANT CONNECT, RESOURCE TO "TEST"; 
-- CREATE VIEW
GRANT CREATE VIEW TO "TEST";
GRANT UNLIMITED TABLESPACE TO "TEST";

Revoke rights again:

REVOKE UNLIMITED TABLESPACE FROM "TEST";

Or set default tablespace + temporary tablespace of an existing user:

ALTER USER "TEST"
  DEFAULT TABLESPACE "TEST"
  TEMPORARY TABLESPACE "TEST_TEMP";

Change user password:

ALTER USER "TEST" IDENTIFIED BY "NEW_PASSWORD";