CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
kaushik
kaushik

Reputation: 2492

How to import data to a specified tablespace

My version of oracle is 11g. I had imported my oracle database to a SQL file called "export.sql". Two days back I reinstalled my machine with windows and reinstalled Oracle also. Now I have created a user named "book" with password "book" and have given privileges using this command:

grant create session, create table to book;

Then I tried to import my SQL file using SQL Developer utility, by opening the export.sql file in the SQL editor. Then it showed me that 

SQL Error: ORA-01950: no privileges on tablespace 'USERS'

Then I thought it would be better to create a different tablespace rather than using the USERS table space. Then I created the tablespace "kwa" using this:

create tablespace kwa
logging
datafile 'G:\kwa\kwa.dbf' 
size 32m 
autoextend on 
next 32m maxsize 2048m
extent management local;

After this I have given privileges to user on that tablespace using this command:

alter user book quota 32m on kwa;

What I want to do now is to make the user "book" use that tablespace in SQL Developer so that I can import my "export.sql" ? , but still when I try to import data it is showing the old "ORA-01950" error. How to change the tablespace for the user "book" in the SQLDeveloper interface ?

Upvotes: 2

Views: 2453

Answers (1)

Yahia
Yahia

Reputation: 70369

try

alter user book default tablespace kwa;

Whether this works depends on whether the export.sql references the USERS tablespace explicitly or not - if it does reference it then you will need to change export.sql !

Upvotes: 4

Related Questions