CODEWITHSUNDEEP
oracle-databasetablespace
sydney
sydney

Reputation: 131

Unable to assign user to tablespace in Oracle 12c

For some reason, I am not able to assign a user to a specific tablespace. I ran the following in Oracle SQL Developer against a local Oracle 12c.

CREATE TABLESPACE tbs_sales
    DATAFILE 'C:\app\oracle\oradata\oradev\sales.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

alter user sales quota unlimited on tbs_sales;

Then I logged on to Oracle SQL Developer as the "sales" user and ran the following statements:

create table Test (col1 int);

select * from user_tables;

It shows the "Test" table belongs to the "USERS" tablespace. I followed the example from this link:

http://www.orafaq.com/wiki/Tablespace

Can someone tell me what I am doing wrong?

Upvotes: 0

Views: 1015

Answers (2)

VelicS
VelicS

Reputation: 49

you should change default tablespace of the user, like

alter user username default tablespace tbs_sales;

Upvotes: 2

Moazzam
Moazzam

Reputation: 429

In your create table statement, specify the tablespace where you want to create table. The default tablespace for sales user is TEST, therefore, table get created here.

use below statement to create table instead:

create table Test (col1 int) tablespace tbs_sales;

Upvotes: 2

Related Questions