OraFAQ Forum: Server Utilities » Importing user in different tablespace on another server

Home » RDBMS Server » Server Utilities » Importing user in different tablespace on another server

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Importing user in different tablespace on another server [message #69838]

Wed, 13 March 2002 03:25

vishal gupta
Messages: 111
Registered: March 2001

Senior Member

Hi,
I am looking solution for the above mentioned.
In brief I have a dump of my data base the all users were made in the default "SYSTEM" tablespace,
Now I want to Import One of the user from the dump on the new oracle 8i database server and my default new tablespace for the user is "FINANCE"
Note:
# I have specified FROMUSER and TOUSER in my parameter file.
# Kindly suggest me how can I import the user's data.
# The user on the new server has no right/previleges to the system table space.

Report message to a moderator

Re: Importing user in different tablespace on another server [message #69842 is a reply to message #69838]

Wed, 13 March 2002 07:32

Grant
Messages: 578
Registered: January 2002

Senior Member

Here is how I move users to another tablespace. I will use the schema scott as an example and the tablespace will be DATA (was in USERS).

exp userid=system/manager parfile=exp_user.par file=exp_scott.dmp log=exp_scott.log owner="(scott)"

Parameter File:
BUFFER=64000
COMPRESS=Y
GRANTS=Y
INDEXES=Y
ROWS=Y
CONSTRAINTS=Y

Enter the following at the SQL prompt:

revoke DBA,RESOURCE from scott; REM make sure they only have connect.
alter user scott quota unlimited on DATA;
alter user scott quota 0M on USERS;
alter user scott quota 0M on SYSTEM;
REM The last three grants give the user added privs that RESOURCE was giving them.
grant create procedure to scott;
grant create trigger to scott;
grant create type to scott;

Now import the user:

imp userid=system/manager parfile=imp_user.par file=exp_scott.dmp log=imp_scott.log fromuser="(scott)" touser="(scott)"

Parameter File:
BUFFER=64000
GRANTS=Y
INDEXES=Y
IGNORE=Y
ROWS=Y

I realize "Y" is the default for the parameters in the par file but there are times you want to change them to "N" so I leave them in. Once you have moved the user you can grant the privileges back to them.

Report message to a moderator

Re: Importing user in different tablespace on another server [message #69852 is a reply to message #69838]

Wed, 13 March 2002 11:48

Grant
Messages: 578
Registered: January 2002

Senior Member

Another point! You have to drop the objects that exist before the import. So you would:

export schema
drop all objects owned by schema or drop/create schema
set up schema for import by setting privileges
import schema

Report message to a moderator

Previous Topic:	changes from online redo logs to the datafile
Next Topic:	SQLLDR and blank interpreted as NULL?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 18:30:45 CST 2025