Unable to assign user to tablespace in Oracle 12c

Question

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?

You can just delete this table. Then create new table with proper tablespace. — Md. Sajedul Karim, Feb 03 '19 at 12:00

score 2 · Answer 1 · answered Apr 29 '16 at 14:08

2

you should change default tablespace of the user, like

alter user username default tablespace tbs_sales;

answered Apr 29 '16 at 14:08

VelicS

49
2

score 1 · Answer 2 · answered Apr 29 '16 at 12:25

1

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;

answered Apr 29 '16 at 12:25

Moazzam

397
1
8
23

or ALTER USER HR DEFAULT TABLESPACE TBS_SALES; – thatjeffsmith Apr 29 '16 at 13:25
I ran the ALTER USER statement. It's working now. Thank you. – sydney May 02 '16 at 14:18

Unable to assign user to tablespace in Oracle 12c

2 Answers2