| exporting table in a different tablespace [message #296900] |
Tue, 29 January 2008 07:36  |
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
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 |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
