How to assign a user just to one tablespace? - Oracle 11g

Question

When installing a new db, it is a best practice to create a new user and a new tablespace, because creating tables shouldn't be done with SYS/SYSTEM or/and on the tablespace "SYSTEM".

So I created the user "alex" and the tablespace "alexData" with the following commands:

CREATE TABLESPACE alexData datafile 'C:/oraclexe/alexData.dbf'
size 100M;
ALTER USER alex QUOTA UNLIMITED ON alexData;
ALTER USER alex QUOTA 0 ON SYSTEM;

I want to accomplish that the user "alex" is only able to save his data in the "alexData" tablespace.

Why? Because when he wants to access a table he shouldn't always have to mention the tablespace "alexData".

otherwise he would always need to mention it like:

SELECT *
FROM alexData.table

but I want that he always is JUST in that tablespace so he doesn't need to mention it:

SELECT *
FROM table;

Is this possible?

Answer 1

First of all , Consequence of what @Mat told, you can not use like

SELECT * FROM alexData.table_ but SELECT * FROM alex.table_, since a table may be prefixed with a schema name. As you may notice, you can not use table,which is a keyword for oracle, as table name, so i've used table_ instead.

When you create user, the object is automatically created inside that tablespace. As an example, when create table ... statement issued, there's no need to mention about the tablespace provided that you create or alter your user with DEFAULT TABLESPACE phrase:

CREATE USER alex IDENTIFIED BY alex321
DEFAULT TABLESPACE alexData
TEMPORARY TABLESPACE alexTempData;

OR

ALTER USER alex IDENTIFIED BY alex321
DEFAULT TABLESPACE alexData;