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

[a***@inbox.com]

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?

[s***@hccnet.nl]

Post by a***@inbox.com
----------------------------------------------
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.

[Mark D Powell]

Post by s***@hccnet.nl

Post by a***@inbox.com
----------------------------------------------
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 --

[steph]

Post by a***@inbox.com
----------------------------------------------
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?

in datapump an option? 10G maybe?

[Noons]

Post by a***@inbox.com
----------------------------------------------
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?

from a very remote corner of my memory:
I think you need to not use a dba account to run
the exp/imp. Then it will take into account
the default tablespace of "touser" and use only
that.

But like I said: very remote corner...

[a***@inbox.com]

Oracle version I'm using is 10g r2+

Using DBA-role accounts results in the same. It uses the default
tablespace for empty tables, but goes against the original tablespace
for the tables with more than 1 row.
With the "quota unlimited" option for all tablespaces or on the source
tablespace, it will create all objects in the original tablespace,
ignoring the "touser"'s default tablespace.

The good news is that data pumps will work instead (if you're lucky to
be on a version that supports these).

The process in such case boils down to the following:

-----------------

expdp ${src_schema} dumpfile=${src_schema}.dmp directory=dmpdir
schemas=${src_schema}

impdp ${dest_schema} dumpfile=${src_schema}.dmp directory=dmpdir \
remap_schema="(${src_schema}:${dest_schema})" \
remap_tablespace="(${src_tablespace1}:${dest_tablespace1},$
{srtc_tablespace2}:${dest_tablespace2})"

sqlplus ${dest_schema} <<!
begin
DBMS_UTILITY.COMPILE_SCHEMA(upper('${dest_schema}'));
end;
/
!

-----------------

It's still slow as mould, but at least it eliminates the need to use
IMP/EXP.

[Mladen Gogala]

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?

Renaming tablespaces???

[a***@inbox.com]

Post by Mladen Gogala

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?

Renaming tablespaces???
--http://www.mladen-gogala.com
--http://www.mladen-gogala.com

Yes, like
alter tablespace ${src_tablespace} rename to ${src_tablespace}_buf;
alter tablespace ${dest_tablespace} rename to ${src_tablespace};

...then run IMP, and after it rename everything back. Looks like an
ugly method.

[StefanKapitza]

Post by a***@inbox.com
----------------------------------------------
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?

IIRC, you have to set additionaly the default Tablespace for the
destination User
(wont work for lob's)

regards

Stefan Kapitza