Re: EXP/IMP to a different tablespace: has anyone been able to solve this?

On Oct 24, 1:19 am, sybra..._at_hccnet.nl wrote:
> On Tue, 23 Oct 2007 13:36:02 -0700, arr..._at_inbox.com wrote:
> >Followed all writeups I've read so far to come with the following:
>
> >----------------------------------------------
>
> >alter user ${dest_schema} quota 0 on ${src_tablespace};
>
> >alter user ${dest_schema} quota unlimited on ${dest_tablespace};
>
> >imp file=${exp_file} ignore=Y fromuser=${src_schema} touser=$
> >{dest_schema}
>
> >----------------------------------------------
>
> >It does indeed create new objects in a new tablespace, but as soon as
> >there's at least 1 row in the export file, it stubbornly attempts to
> >go back to the src_tablepace (original user's tablespace data was
> >exported from) and generates ORA-01536: space quota exceeded for
> >tablespace error.
>
> >Is there a clean solution, or each time I need to mess with renaming
> >all tablespaces before imp and then renaming them back after imp?
>
> Never seen this, and as your post is pretty scarce on details (no
> version, no information about how the user where set up (they don't
> have unlimited tablespace privilege, do they) no command line) it
> boils down to
> It doesn't work
> and requires a crystall ball
> Provide more details or precreate all objects, whatever you prefer.
>
> --
> Sybrand Bakker
> Senior Oracle DBA- Hide quoted text -
>
> - Show quoted text -

When you need to change the tablespace that a table is in to be different from what it was when the traditional exp utitlity dump file was made then the best solution to to pre-create the table where you want it and then use the ignore=y option on the import. Do not forget the indexes.

The show or indexfile options of imp can be used to generate table and index source if you do not have access to it or to the Oracle environment where the objects were exported from.

With 10g and impdp/expdp you have some additional features that I think can do this but I do not have time to look. See the utilities manual if you have 10g and the export was made with expdp.

HTH -- Mark D Powell -- Received on Wed Oct 24 2007 - 09:59:48 CDT