RE: DataPump Imports and Tablespace Quota

Hi Finn

Both the datapump export and import was at the schema level and I used system for both. Here is the top of the datapump import log file:

;;;
Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 29 May, 2008 16:49:39

Copyright (c) 2003, 2005, Oracle. All rights reserved. ;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options Master table "SYSTEM"."SYS_IMPORT_SCHEMA_03" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_03": system/******** dumpfile=srs.dmp logfile=srs_impdp.log schemas=srs Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA ORA-39083: Object type PROCACT_SCHEMA failed to create with error: ORA-31625: Schema SRS is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV ','CURRENT_SCHEMA'), export_db_name=>'PROD', inst_scn=>'7241480204647');COMMIT; END;

Thanks,

Sam Bootsma
Oracle Database Administrator
Information Technology Services
George Brown College
Phone: 416-415-5000 x4933
Fax: 416-415-4836
E-mail: sbootsma_at_georgebrown.ca

-----Original Message-----
From: Finn Jorgensen [mailto:finn.oracledba_at_gmail.com] Sent: May 31, 2008 2:36 AM
To: Sam Bootsma
Cc: oracle-l_at_freelists.org
Subject: Re: DataPump Imports and Tablespace Quota

Sam,

Could you please paste the first few lines from your import session so we can see the error you are receving. Do not cut anything from the top and include enough to get to the error you are describing about the user not existing.

If the datapump export was a schema level export and the import is a full import, done as a different user that has privs to create the schema, this should all work.

Finn

On 5/30/08, Sam Bootsma <sbootsma_at_georgebrown.ca> wrote:
>
>
>
> I was asked recently to export a schema from PROD and import into
DEVL. No
> problem, I thought, I have done this many times before, except
importing
> into TEST. I have even automated it to some degree.
>
>
>
> I did the export from PROD (using data pump), and ftp'd the file to
the box
> hosting our DEVL database. I dropped the schema from DEVL, then tried
to
> import (using data pump again). To my surprise I received an error
saying
> the user did not exist. I had never encountered this error when doing
the
> same process against TEST. I eventually fixed this problem by
creating the
> schema in DEVL and granting it quota on the USERS tablespace. Now the
> datapump import worked.
>
>
>
> Question is, why didn't I have to create the user in TEST and grant
quota?
> In TEST the process was slightly different. I would shutdown the
Windows
> Service for the application then I would manually kill the 4 or 5
database
> sessions for this user. I think that after this v$session would show
the
> session as "killed". After this I would drop the user then import
using
> data pump. And it always worked. I never needed to manually create
the
> user and grant quota on the USERS tablespace. I confirmed the schema
does
> not have the unlimited tablespace privilege in TEST or PROD that would
> automatically give it quota in USERS. The only thing I can think of
is that
> on the TEST database Oracle has not completed the cleanup yet because
> v$session still shows a status of "killed" for the user. And that this
> somehow enabled the user to be imported without quota.
>
>
>
> Does anybody else have insights on this anomaly? Thanks!
>
>
>
> All databases are Oracle 10.2.0.3 on AIX 5.3.
>
>
>
> Sam Bootsma
>
> Oracle Database Administrator
>
> Information Technology Services
> George Brown College
>
> Phone: 416-415-5000 x4933
> Fax: 416-415-4836
> E-mail: sbootsma_at_georgebrown.ca
>
>

--
http://www.freelists.org/webpage/oracle-l