I was asked several times how to create a new tablespace and assign it to a new user with permission in Oracle XE. Not being a DBA it took me some trial & error to get it working. For future reference (primarily for myself) I'll share the commands I used here.
Creating an Oracle XE tablespace
Execute the following SQL command on the Oracle XE database:
create tablespace MYTABLESPACE datafile '<path>' size 32m autoextend on next 32m maxsize 2048m extent management local;
In windows the
<path> can be something like
C:\oraclexe\app\oracle\oradata\XE\MYTABLESPACE.dbf and in linux
.dbf file should be created automatically. If it doesn't, check if the path is valid.
It should go without saying you should change the quota to fit your needs.
Creating a user and granting permissions
Run the following SQL statements to create a
MYTABLESPACE-user with common permissions:
CREATE USER MYTABLESPACEUSER IDENTIFIED BY MyPassword123 DEFAULT TABLESPACE "MYTABLESPACE" TEMPORARY TABLESPACE "TEMP"; ALTER USER MYTABLESPACEUSER QUOTA UNLIMITED ON MYTABLESPACE; GRANT create procedure, create session, create table, create type, create view, create synonym, create trigger, resource TO MYTABLESPACEUSER;