FW: Table belongs to Original tablespace after export with DataPump

From: "Mark W. Farnham" <mwf@xxxxxxxx>
To: "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
Date: Fri, 30 Jan 2009 08:19:32 -0500

oops. left the list off.

  _____  

From: Mark W. Farnham [mailto:mwf@xxxxxxxx] 
Sent: Thursday, January 29, 2009 11:39 PM
To: 'gheibia@xxxxxxxxx'
Subject: RE: Table belongs to Original tablespace after export with DataPump

I thought you needed apostrophes around the string values, like this:

REMAP_TABLESPACE='tbs_1':'tbs_6'

(that's from the doc example)

If that doesn't work for you, consider creating an indexfile, use something
like sed to turn TABLESPACE "old_tablespace_name" into TABLESPACE
"new_tablespace_name", then run the modified script to create the objects,
and then use data_only parameter to datadump the data in.

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Amir Gheibi
Sent: Thursday, January 29, 2009 9:58 AM
To: ORACLE-L
Subject: Table belongs to Original tablespace after export with DataPump

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

FW: Table belongs to Original tablespace after export with DataPump

Other related posts: