Home » RDBMS Server » Server Utilities » Importing user in different tablespace on another server
Importing user in different tablespace on another server [message #69838] Wed, 13 March 2002 03:25 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 01:22:15 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.