| how to move user data to different tbs? [message #69032] |
Wed, 17 October 2001 11:38  |
George
Messages: 68
Registered: April 2001
|
Member |
|
|
I need to move a user's data to a different tbs, I tried following way but the import still goes back to the system tbs.
1. CREATE TABLESPACE scotttbs
DATAFILE 'F:OracleOradatadevlscott01' SIZE 2M
DEFAULT STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0 MAXEXTENTS 20)
online;
2. //exp
3. ALTER USER scott DEFAULT TABLESPACE scotttbs
TEMPORARY TABLESPACE TEMP
quota 0 on SYSTEM
quota 2M on scotttbs ACCOUNT UNLOCK;
4. drop scott tables
5. imp
Thanks
----------------------------------------------------------------------
|
|
|
|
| Re: how to move user data to different tbs? [message #69036 is a reply to message #69032] |
Thu, 18 October 2001 03:07  |
nagesh d babu
Messages: 7
Registered: October 2001
|
Junior Member |
|
|
There are two ways:
1. After you import the user, connect to scott and do the following:
a. select 'alter table '||table_name|| ' move
tablespace scotttbs;' from cat;
b. spool the above statement and run. The
tables will be moved from system to scottbs.
2. Open the .dmp file and delete the word tablespace "system" by just using find and replace, then import. Now the objects will be created under default tablespace.
Chinnu
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
