Home » RDBMS Server » Server Utilities » exporting table in a different tablespace
exporting table in a different tablespace [message #296900] Tue, 29 January 2008 07:36 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Dear All,

i want to export a scott's table test (exist in system tablespace ) ,drop this table and then import this table into a different tablespace (e.g users tablespace ) but in the same schema which is scott
please see what i had done

step1:
First check the scott's default tablespace

select username,default_tablespace
from dba_users
where username='SCOTT'
USERNAME    DEFAULT_TABLESPACE
---------------------------------------------
SCOTT	     SYSTEM


step2:
logged in as user scott and create a table test as

create table test as select * from emp;

and now check that table is created in SYSTEM TS because the scott's default ts is system

select table_name,tablespace_name
from user_tables
where table_name='TEST'

TABLE_NAME      TABLESPACE_NAME
----------------------------------
EMP	                SYSTEM


Step3:
Now change the scott default ts as users
alter user scott default tablespace users


Step4:
and now export the table
exp scott/tiger tables=test file=d:\oracle\test_export.dmp \
log=d:\oracle\test_export_log.log direct=y 


Step5:
Now drop the table test
SQL>drop table test;


Step6:
now import the table
imp scott/tiger file=d:\oracle\test_export.dmp tables=test 

but now if i check in which tablespace it is imported ,it shows the tablespace system

select table_name,tablespace_name
from user_tables
where table_name='TEST'

TABLE_NAME      TABLESPACE_NAME
--------------------------------------
EMP	                SYSTEM

i was expecting the test table to be imported in users ts which is default tablespace for scott now ?
Please suggest how to do it to import test table in users ts ?
is there any option available in imp

[Updated on: Tue, 29 January 2008 12:36] by Moderator

Report message to a moderator

Re: exporting table in a different tablespace [message #296909 is a reply to message #296900] Tue, 29 January 2008 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove UNLIMITED TABLESPACE privilege or RESOURCE or DBA role from SCOTT, grant him a quota on USERS and 0 on SYSTEM and it will work.

Regards
Michel
Re: exporting table in a different tablespace [message #296962 is a reply to message #296900] Tue, 29 January 2008 12:22 Go to previous messageGo to next message
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
Member
In your step 2

select table_name,tablespace_name
from user_tables
where table_name='TEST'

returns the table_name = EMP which is in SYSTEM tablespace

Similarly in step 6

Please post the correct output from your test
Re: exporting table in a different tablespace [message #296963 is a reply to message #296962] Tue, 29 January 2008 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the correct and expected output if user has UNLIMITED TABLESPACE privilege.

Regards
Michel

[Edit: "r" missing in "user"]

[Updated on: Tue, 29 January 2008 13:05]

Report message to a moderator

Re: exporting table in a different tablespace [message #296967 is a reply to message #296963] Tue, 29 January 2008 12:54 Go to previous message
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
Member
Thanks Michel
Previous Topic: generate DDL for tablespace objects
Next Topic: Problems setting up Dumpfile Location
Goto Forum:
  


Current Time: Sat Dec 03 21:59:47 CST 2016

Total time taken to generate the page: 0.08303 seconds