 Import to another tablespace oracle10G [message #151444] |
Wed, 14 December 2005 21:20  |
chara
Messages: 81
Registered: April 2005
Location: th
|
Member |
|
|
Dear guys,
I tried to import data to another tablespace and another schema.The first i tried to used "alter user newuser default tablespace newtbs ;" but after imported I found the data still stored old tablespace.what happened? How to solve it?
Thks for advance
Chara
|
|
|
|
|
|
|
|
| Re: Import to another tablespace oracle10G [message #151477 is a reply to message #151474] |
Thu, 15 December 2005 01:05  |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
Why you imported it with FULL=Y, its used for full database import and not at all required for importing a user's schema.
i am also able to replicate it. look here
SQL> create user newbie identified by newbie
2 default tablespace newbie;
User created.
[oracle@localhost ~]$ exp scott/tiger file=/data/exprt/scottexp.dmp
[oracle@localhost ~]$ imp system/manager file=/data/exprt/scottexp.dmp fromuser=scott touser=newbie
Import: Release 9.2.0.4.0 - Production on Thu Dec 15 12:20:54 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by SCOTT, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing SCOTT's objects into NEWBIE
. . importing table "BONUS" 0 rows imported
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
. . importing table "PLAN_TABLE" 7 rows imported
. . importing table "SALGRADE" 5 rows imported
. . importing table "T1" 0 rows imported
About to enable constraints...
Import terminated successfully without warnings.
SQL> select segment_name,owner, tablespace_name
2 from dba_segments
3 where owner='NEWBIE';
SEGMENT_NAME OWNER TABLESPACE_NAME
-------------------- ------------------------------ --------------------
BONUS NEWBIE USERS
DEPT NEWBIE USERS
EMP NEWBIE USERS
PLAN_TABLE NEWBIE USERS
SALGRADE NEWBIE USERS
T1 NEWBIE USERS
PK_DEPT NEWBIE USERS
PK_EMP NEWBIE USERS
8 rows selected.
But its strange, I never noticed it.
Lets wait for others to chip in.
regards,
tarun
|
|
|
|
| Re: Import to another tablespace oracle10G [message #151488 is a reply to message #151477] |
Thu, 15 December 2005 02:15 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Not strange at all.
It is the way it is supposed to work.
We have dealt with this here many times.
Search the forum.
http://www.orafaqcom/forum/t/27126/0/
Reason is RESOURCE ROLE.
When you create the user , you must have granted the resource role.
RESOURCE ROLE allows the user to write to any tablespace available even if it is NOT his default tablespace.
During import, the dump file is read.
The tables are recreated based on their DDL inside the dump.
With resource role available, during import the tables and obejcts are recreated in the same tablespace as before ( as in original DDL).
So,
create new user.
grant only connect role.
Grant specific privs.
allocate a default tablespace.
allocate a quota on tablespace.
try again.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
