jmodic help please

[sonaliak]

I usually do not write any posts with specific names.
But I need your help today. It is kind of urgent.

Please read my import failed post !!!

thanks

[jmodic]

Yes, I hate this too, realy. Anyway, reed my guesses in your original thread.

[amar]

Hi all,

I don't know whether to post a reply here or on the original post! But decided to write here.

Sonali, this is not a case of "peculiar" failure. What is happening is:

When you export, the table creation script is also incuded in the export file. Now the sql statement for creating the table (and any other objects as necessary) always has the "TABLESPACE" clause of the "CREATE TABLE" sql statement attached to it. In your case it will be "TABLESPACE PRODUCTIION".

Verify this by running a command similar to the following:

$ strings expdat.dmp | grep "CREATE TABLE"

When you are trying to import, because you already have the PRODUCTION tablespace the objects are being created there. Had the PRODUCTION tablespace not been there you would have got the error at the first object itself. Otherwise also, if TEST user did not have DBA privileges, you would have got the error at the first object. But being a DBA, TEST user is able to create tables in any tablespace and so the objects are being created and when no space is available you are getting the error.

What you can do is to create a sql script containing all the create table statements and replace the tablespace name with TEST in place of PRODUCTION, or eliminate the TABLESPACE clause altogether.

-amar