OraFAQ Forum: Server Administration » DATABASE IMPORT IN THE DEFAULT TABLESPACE OF USER

Home » RDBMS Server » Server Administration » DATABASE IMPORT IN THE DEFAULT TABLESPACE OF USER

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

DATABASE IMPORT IN THE DEFAULT TABLESPACE OF USER [message #58571]

Thu, 18 September 2003 01:42

VISHNU
Messages: 14
Registered: November 2001

Junior Member

Hi,

I have exported database into a dump file ,the exported database was using the system table space.

Now i am trying to import database from the above dump file into the new user which has default table space specified, but its the import is happening in system table space rather then the default tablespace.

On the command prompt command i am running this command to import:

imp user/password@netservicename file=filename fromuser=x1 touser=xyz ignore=y

thanks and regards,

vishnu

Report message to a moderator

Re: DATABASE IMPORT IN THE DEFAULT TABLESPACE OF USER [message #58572 is a reply to message #58571]

Thu, 18 September 2003 06:21

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

wierd~.
try this.
you may have granted the target user RESOURCE role which will allow him to write to any tablespace ( unlimited quota).
REVOKE RESOURCE ROLE from target user.
allocate quotas ON THE DEFUALT TABLESPACE ONLY to target user.
now try import again.
theoritcally, since the user has no quota on any tablespace excpet his defualt, the objects shoule get imported into the default tablespace or error out.
note that by revoking resource role, certain privs like create trigger will also be revoked. so grant them explicitly again.

Report message to a moderator

Re: DATABASE IMPORT IN THE DEFAULT TABLESPACE OF USER [message #58573 is a reply to message #58571]

Thu, 18 September 2003 06:23

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

well this is not wierd.
the objects already exist in system tablespace.
since the user has resource role in both source and target databases, he can write to system tablespace.~!

Report message to a moderator

Re: DATABASE IMPORT IN THE DEFAULT TABLESPACE OF USER [message #58581 is a reply to message #58571]

Thu, 18 September 2003 14:52

Sanjay
Messages: 236
Registered: July 2000

Senior Member

The user doing the import may have had 'resource' which give quota unlimited to all tablespaces (as mentioned by Mahesh)

Best bet would be to give the user 'connect' and 'resource'. Then revoke the 'unlimited tablespace'

create user user_a
default tablespace ts_a
temporary tablespace temp
quota unlimited on ts_a
quota unlimited on temp;

grant resource to user_a;
grant connect to user_a;
revoke unlimited tablespace from user_a;

NOTE: To make sure that user_a DOES NOT have any access to SYSTEM tablespace, create a table in that tablespace. If you get an error, you do not have any access.

This way you are making sure that no objects go to tablespace SYSTEM.

Report message to a moderator

Previous Topic:	I Need to Know, but its too much,
Next Topic:	Partitioning Option not enabled but I'm able to create partitions ??!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 15:32:51 CDT 2024