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.

  • Tables, indexes and partitions reside in a tablespace - When you create a user you determine which default tablespace the user will use when the user creates an object (table or whatever). I think you might not fully understand the whole concept of how data is stored in Oracle. I suggest you read this docs.oracle.com/database/121/ADMIN/tspaces.htm – Philᵀᴹ Nov 6 '17 at 10:14
  • Just out of curiosity, what are the issues with Firebird that are causing you to migrate? – Vérace Nov 6 '17 at 11:09

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:

--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:
SQL> grant create table to user1;

Grant succeeded.

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

Table created.

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

Table created.
| improve this answer | |
  • Thanks a lot JSapkota, that was the point yes, that, how can I select the way in which tablespace to create a table or select data. I understand that a TS it is just a logical flag, and I have to use its name in each query or DDL language if I want to decide where to execute it. For me it is important to keep separately the data from the metadata but it depends on datafiles(physically) attached to tablespaces which I can handle now;) Thanks again. – Montazsy Nov 6 '17 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.