Home » RDBMS Server » Server Utilities » Importing to different tablespace with no quota
Importing to different tablespace with no quota [message #243161] Wed, 06 June 2007 05:10 Go to next message
tayalarun
Messages: 20
Registered: December 2005
Junior Member
Hi,

I am using Oracle 10.2.0.2.0 on Sun Solaris server.

I have received a export dump file of user "SUN"
The "SUN" user is using tablespace "SUN_DATA".

Here in my database, I have created a new tablespace "SUN_NEW_DATA"
and created a new user "SUN_NEW".
I have granted quota unlimited on SUN_NEW_DATA to "SUN_NEW" user.
I have not given any quota on "SUN_DATA" to "SUN_NEW" user.
Though my database is also having "SUN_DATA" tablespace which is being used by other users.

Now when I give the command :
imp sun_new/sun_new file=sun_exp.dmp log=sun_exp.log fromuser=sun touser=sun_new

I am seeing that all the tables are created in "SUN_DATA" tablespace.

Can someone explain me why the import have created objects in "SUN_DATA" tablespace though
the user "SUN_NEW" does not have any quota on that tablespace.


Thanks & Regards
Arun Tayal
Re: Importing to different tablespace with no quota [message #243163 is a reply to message #243161] Wed, 06 June 2007 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You granted him unlimited tablespace possibly through resource or dba role.

Regards
Michel
Re: Importing to different tablespace with no quota [message #243177 is a reply to message #243163] Wed, 06 June 2007 06:14 Go to previous messageGo to next message
tayalarun
Messages: 20
Registered: December 2005
Junior Member
Hi Michel,

I have given the following priveledges to "SUN_NEW" user

Commands given from System user :
Create role sun_role;
Grant create table, create sequence, create view, Create Procedure, create type, Create trigger
to sun_role;
Grant Create Session to sun_role;
Grant sun_role to sun_new;

I have not given Resource role or DBA role to SUN_NEW user.


Arun
Re: Importing to different tablespace with no quota [message #243217 is a reply to message #243177] Wed, 06 June 2007 08:40 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post:
select tablespace_name, max_blocks from dba_ts_quotas where username='SUN_NEW' order by 1
/
select grantee, privilege from dba_sys_privs where grantee in ('SUN_NEW','PUBLIC') order by 1, 2
/
select grantee, granted_role from dba_role_privs where grantee in ('SUN_NEW','PUBLIC') order by 1, 2
/

Regards
Michel
Previous Topic: ime, exp query
Next Topic: Import data in oracle 9i
Goto Forum:
  


Current Time: Thu Dec 08 22:01:32 CST 2016

Total time taken to generate the page: 0.05122 seconds