Re: imp-exp tools

In article <8vbab1$939$1_at_nnrp1.deja.com>,   carl_bruneau_at_my-deja.com wrote:
> In article <8v7824$cn0$1_at_nnrp1.deja.com>,
> faheemrao_at_my-deja.com wrote:
> > In article <8v4915$8i3$1_at_nnrp1.deja.com>,
> > carl_bruneau_at_my-deja.com wrote:
> > >
>
> Hello Faheem, fisrt of all, thank's for your help with this one:
>
> > I have red the Both suggestion to problem , they may be correct. But
 to
> > my understanding that it is not neccassry that you have to have the
> > tablespace with the same name , from which the export(dmp) file has
> > been generated, you can import that dmp file to any user regardless
 of
> > its defaul tablespace name.
> > I think you have not granted the quota to the user in which you want
 to
> > import the dmp file , so make sure that you must have grant the
 quota
> > to the user to any tablespace you need.
>
> All our users have unlimited quota on their default tablespaces.
>
> >
> > You can also grant role "resource" to the to_user.
> >
> > try
> >
> > sql> grant resource to to_user;
> >
> > then try the import.exe file , I think you might not get any error.
>
> It work. Now, can you tell me why? If I take a look at the resource
> role, I see that it include those privileges:
>
> create cluster
> create indextype
> create operator
> create procedure
> create sequence
> create table
> create trigger
> create type
>
> Among those there is 3 privileges included in the connect role wicth
> every of our users have: create cluster, create sequence and create
> table.
>
> If I try to grant just those 5 left to the "touser", it doesn't work.
 Is
> it just related to the fact that the user have the resource role?
 Seems
> weird to me.
>
> Thank's a lot.
>
> Carl
>
> > Faheem
> >
> > Hello Everyone,
> > >
> > > In our business - software developper - we have to deal with data
 from
> > > all of our clients. Consequently we decided long ago to work with
 many
> > > tablespaces on the same server and many users by tablespace. So
 every
> > > tablespace logically represent a set of client.
> > >
> > > Recently, we upgrade our Oracle server from version 8.0.3 to
 version
> > > 8.1.6.
> > >
> > > Since then, we have a problem using the imp-exp tools.
> > >
> > > Before the upgrade, we were able to copy all the objects from a
 users
> > > schema residing on one tablespace to another user residing on
 another
> > > tablespace on the same server. Here is an exemple:
> > >
> > > exp.exe system/manager owner=user_a file=x.dmp log=y.log direct=y
> > > consistent=y
> > >
> > > imp.exe system/manager fromuser=user_a touser=user_b file=x.dmp
> > > log=z.log commit=y
> > >
> > > We always did that with exp-imp tools using the fromuser and
 touser
> > > parametrers and it always worked.
> > >
> > > Since the upgrade, we ancounter the imp-00015 error when we are
 doing
> > > that kind of move. Oracle says to us that the default behavior of
 those
> > > tools was to try to copy the data to the same tablespace as it was
> > > extract with exp and that those tools are not intended for this
 kind
 of
> > > move. They almost didn't believe us that we were able to do that
 on
 our
> > > old server (but, of course, they didn't want to try to reproduce
 it).
> > >
> > > So, I would like to know if you have to deal with this kind of
 situation
> > > and how you proceed to do that?
> > >
> > > Any suggestions?
> > >
> > > Thank's a lot,
> > >
> > > Carl
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

The RESOURCE grant is a "sticky wicket". so to speak, as it also grants access to the SYSTEM tablespace which is not a good thing for regular users to have. Should the default tablespace for a user not be reassigned  from the Oracle default of SYSTEM one can wreak havoc on a database by creating user tables in the SYSTEM tablespace (and, believe me, I have seen this happen a number of times). I wouldn't go about granting RESOURCE to all of your user accounts. It can be a quite dangerous grant to issue.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.