One oracle user two tablespace issue

Question

I have an application which works with two separate firebird database, but each database has the same user, but in case of Firebird the connection could be driven to each by connection string. My question is that if I would like to do the same in oracle, how can I do it? I created two tablespace(one for matadata one for data). User 'User1' has access to both of them, but how can I handle that once I want to read/write in Mata TS once in the DATA TS? I need to create a new user for the second one, or there is a way to manage it?
I would be surprised if there was no solution because then why would it allow a user to have several tablespace if I can not control where to write? Thank you in advance for the answers.

Answer 1

A tablespace is a logical entity in Oracle database which consists of physical data files stored on the underlying filesystem.

We provide a quota of a certain size on the tablespace to a user. Which allows the user to create objects on it. And the user can explicitly defines on which tablespace he/she wants to create the object.

Here is a simple example which represents(as far as I understand) your scenario.

-- Create two tablespaces
SQL> create tablespace meta_data datafile size 1m;

Tablespace created.

SQL> create tablespace data datafile size 1m;

Tablespace created.

--Create a user and assign quota on one of the tablespaces 
--or you can define a tablespace a default tablespace 
--on which user can create an object by default.

SQL> create user user1 identified by user1 quota unlimited on meta_data
  2  ;

User created.

--Assign quotas on another tablespace
SQL> alter user user1 identified by user1 quota unlimited on data;

User altered.

--Now, the user can create objects on both of the tablespaces. 
--In order to make a connection, he or she needs to have 
--`create session` system privilege

SQL> grant create session to user1;

Grant succeeded.

SQL> conn user1@orclpdb
Enter password:
Connected.

--Let's create a table on meta_data tablespace.

SQL> create table t1(col1 number) tablespace meta_data;
create table t1(col1 number) tablespace meta_data
*
ERROR at line 1:
ORA-01031: insufficient privileges

--Oops, lack of create table privilege

SQL> conn sys@orclpdb as sysdba
Enter password:
Connected.
SQL> grant create table to user1;

Grant succeeded.

SQL> conn user1@orclpdb
Enter password:
Connected.
SQL>  create table t1(col1 number) tablespace meta_data;

Table created.

SQL> create table t2(col1 number) tablespace data;

Table created.