Re: Import bug

Chuck wrote:
>
> "Knut Talman" <knut.talman_at_mytoys.de> wrote in message
> news:3D1C8527.59974CC4_at_mytoys.de...
> > Chuck wrote:
> > >
> > > "Knut Talman" <knut.talman_at_mytoys.de> wrote in message
> > > news:3D1C7294.35C174C0_at_mytoys.de...
> > > > Chuck wrote:
> > > > >
> > > > > I believe I've just discovered a bug in the import process.Please
> tell
> > > me if
> > > > > you agree. In the case where a user owns tables but has no quota on
> any
> > > > > tablespace, you can export that user but when you try to import it
> the
> > > > > tables will not be created. Even running the import as a DBA user,
> you
> > > still
> > > > > cannot import the the tables.
> > > >
> > > > The user has *no* quota and his tables can't be imported? Which error
> > > messages?
> > >
> > > IMP-00017: following statement failed with ORACLE error 1536:
> > > "CREATE TABLE "PSDBOWNER" ("DBNAME" VARCHAR2(8) NOT NULL ENABLE,
> "OWNERID"
> > > V"
> > > "ARCHAR2(8) NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
> 255
> > > "
> > > "LOGGING STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS
> 2147483645
> > > "
> > > "PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
> > > "
> > > " "
> > > IMP-00003: ORACLE error 1536 encountered
> > > ORA-01536: space quota exceeded for tablespace 'USERS'
> >
> > What happens if you execute the statement directly in SQL*Plus when
> connected as
> > the user? Did you try to explicitly remove any quota?
>
> In sqlplus it fails because use PS (the owner of the PSDBOWNER table) has 0
> quota on the tablespace. Even if attempred as a DBA user. My point is that
> if this is the way import works, it's unreliable is a tool for restoring a
> database. The database if recreated is left missing objects that were in the
> database when it was exported.

Now I understand. I thought user PS has no quota set, but in fact this user has a quota of 0! Of course imp will fail as any other tool will. If the user can not allocate an extent in tablespace USERS, how should he be able to create a table. And even if a user with DBA privs tries to create a table for him (create table pb.psdbowner...) it will fail, because Oracle looks at the quota of PB, not the quota of the DBA.
Delete the quota, import the tables, set a quota. Or rethink your quota and security settings.

Hth,

Knut Received on Fri Jun 28 2002 - 11:27:22 CDT