DataPump Imports and Tablespace Quota

  • From: "Sam Bootsma" <sbootsma@xxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 30 May 2008 11:28:35 -0400

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@xxxxxxxxxxxxxx <mailto:sbootsma@xxxxxxxxxxxxxx> 

 

Other related posts: