Re: Table belongs to Original tablespace after export with DataPump

From: David Ballester <ballester.david_at_gmail.com>
Date: Thu, 29 Jan 2009 16:31:32 +0100
Message-Id: <1233243092.6364.3.camel_at_nebuchaddnezzar>

El jue, 29-01-2009 a las 22:57 +0800, Amir Gheibi escribió:
> Hi listers,
>
>
> I have a 10g DB on HP-UX. I used data pump to make a copy of one of
> the tablespaces. So I exported from one and imported into another one:
>
>
> $ expdp user1/pass1 schemas=user1 directory=dump_dir
> dumpfile=exp.dmp logfile=expLog.log
>
>
>
> $ impdp user2/pass2 directory=dump_dir dumpfile=exp.dmp
> logfile=impLog.log REMAP_SCHEMA=user1:user2
> REMAP_TABLESPACE=tblspc1:tblspc2
>
>
>
>
>
> User1's default tablespace is tblspc1 and User2's default tablespace
> is tblspc2.
>
>
> What happens is that the "tablespace" property of the imported tables
> owned by "User2" don't change as they are just pointing back to the
> original tables in "tblspc1". Are'nt the imported tables supposed to
> be copied to the destination tablespace?
>
>
> I logged in as "User2" and ran:
>
>
> $ select table_name, tablespace_name from user_tables where table_name
> = 'MYTBL'
>
>
> TABLE_NAME TABLESPACE_NAME
> ---------------------------------------------------------
> MYTBL tblspc1
>
>
>
>
> Shouldn't the tablespace_name be "tblspc2"? I thought the data pump
> creates the table in the destination tablespace and then import the
> data into it.
> How should I prevent that problem at the import time?
>
>
> Regards,
> Amir Gheibi

Alter table .... modify default attributes tablespace <tablespace >

if this table has partitions, use:

Alter table ... modify default attributes for partition <partition> tablespace <tablespace>

HTH D.

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

Received on Thu Jan 29 2009 - 09:31:32 CST