Re: Import into a new table space is BROKEN!

In article <drt.716542402_at_brolga>, drt_at_brolga.cc.uq.oz.au (David Taylor) writes:
>
> I am having a problem with some undocumented features of import.
>
> The export file came from version 6somethingorother on a VAX. It was
> a straight user export.
> I want to import the tables under a different username on a DOS box
> also running version 6somethingorother.
>
> I created a special tablespace for the data (~200M) on a new disk.
> I created a user and granted resource access to the table space.
> I made the new table space the default for this user.

When you GRANTed access for the user did you give them RESOURCE and/or DBA access?

If you give them RESOURCE then they can put objects anywhere. Try revoking resource from the user. GRANT/REVOKE access to a tablespace is different to GRANT/REVOKE access for a user id.  

> When I run import it appears to work, but it creates
> the tables in the SYSTEM table space, and falls over eventually
> because there isn't enough space there. The manual clearly states
> that it should load into the default table space for this user.
> It even gives an example of how to use this feature to transfer
> stuff between table spaces.

I think IMPORT looks to see where the original tables came from and tries to put them back into that TABLESPACE first and if a TABLESPACE of that name doesn't exist it looks for SYSTEM and if you don't have RESOURCE or Resource access to SYSTEM then it sticks the object in your Default TABLESPACE.

If I'm wrong then please EM me as this appears to be how it has been working for me.

> Tricky little feature that one. Lets try creating a table in the
> system table space for this user and fill it with junk. Works fine.

YES. This indicates the user has RESOURCE and/or DBA privileges. I'm pretty sure that's your problem.

> The dba_ts_quotas view shows some strange numbers. The manual says NULL
> means unlimited quota but we actually have -1024 bytes. Doesn't seem
> to matter anyway, 'cause if we grant 0 bytes it still lets us have
> as much as we want.
>
> Oh well, so quotas are meaningless. Lets get back to the import problem.
>
> Run import again _interactively_ as the new user, and load the tables
> from the old user. Again, fills the system table space.
>
> Run it using _command line_ parameters, as the new user, and load the
> tables from the old user:
> ERROR: must be dba to import objects to another user's account.
> Huh?!?? I just did it _interactively_ and I wasn't dba.
>
> Run it as dba. Again fills the system table space.
>
> My observations:
> I immediatly suspect that I set up the table space/username incorrectly,
> but from sqlplus everything works as it should.
>
> So maybe import has some features. SOmeone must have found them by now.
> Is there a way around it?
>
> The strange ignorance of quotas is disturbing. If the work around
> involves giving the user a 0 quota on SYSTEM then I'm stuck, because
> it appears to be meaningless.

I think IMPORT bypasses a lot of checks (eg quotas and passwords) to do its job. Maybe someone from Oracle Corp. can spread some light on this.

-- 

Bruce...        pihlab_at_hhcs.gov.au
                 ^^
*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Health, Housing & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************