Skip to Main Content
  • Questions

  • REMAP_TABLESPACE not working during impdp

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Naresh.

Asked: January 07, 2019 - 12:34 pm UTC

Last updated: August 09, 2019 - 6:51 am UTC

Version: 12.2.0.1

Viewed 10K+ times! This question is

You Asked

Hello,

I have a Pluggable database OTB1 with multiple schemas with different tablespaces. Tablespaces names are in lowercase. (SCHEMAS AND TABLESPACES NAMES ARE SAME, BUT ONE IN LOWER CASE AND OTHER IN UPPER CASE)

SQL> select username from dba_users;

TABLESPACE_NAME
------------------------------
X3DPASSADMIN
X3DPASSTOKENS
X3DDASHADMIN

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
x3dpassadmin
x3dpasstokens
x3ddashadmin


I have exported the schemas using expdp 

expdp system/xxxxx@OTB1 schemas=x3dpassadmin dumpfile=x3dpassadmin_01072019.dmp logfile=x3dpassadmin_01072019.log


I have created a new PDB (REPLICA) in different oracle oracle server and trying to import the schemas by remap_schema and remap_tablespace.

impdp system/xxxxxx@REPLICA directory=backup dumpfile=x3dpassadmin_01072019.dmp schemas=x3dpassadmin REMAP_TABLESPACE="x3dpassadmin":"repx3dpassadmin" REMAP_SCHEMA=X3DPASSADMIN:REPX3DPASSADMIN


import fails with the following error:

**************************************************

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@REPLICA directory=backup dumpfile=01072019.dmp schemas=x3dpassadmin REMAP_SCHEMA=x3dpassadmin:repx3dpassadmin REMAP_TABLESPACE=x3dpassadmin:repx3dpassadmin 
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-39083: Object type USER:"REPX3DPASSADMIN" failed to create with error:
ORA-00959: tablespace 'x3dpassadmin' does not exist

Failing sql is:
 CREATE USER "REPX3DPASSADMIN" IDENTIFIED BY VALUES 'S:22631040B826012059205A147CE70F13DE963B5DFFAF7534DFE7FB6A9D1A;T:260AF81B044D16D04197CED00B9EE62BFC7A6BF3D8E73762E7695969A3965695830390E3D0D854019B621ADCB6277951971997F4E62F97EA26C3D2E9D551E9832A16E2804FAAA54417B638D650409944' DEFAULT TABLESPACE "x3dpassadmin" TEMPORARY TABLESPACE "TEMP"



**************************************************

while creating the users, default tablespace is getting assigned as old one, so remap_tablespace is not working ? Can you please help ?

Regards,
Naresh

and Chris said...

1. You need to create the tablespace before doing the import. A schema mode import won't create them.

2. You may run into issues with lowercase names on the command line for impdp. From MOS note 2464695.1:

The tablespace TEST_TBS exists in the database, but the tablespace name is created with lower case:
SQL> create tablespace "test_tbs" datafile '<path>/test1.dbf' size 10m logging;

Tablespace created.

SQL> select TABLESPACE_NAME from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UD1
TEMP
SYSEXT
USER_DATA
test_tbs

DataPump Utility doesn't find the lower case tablespace during its execution and reports ORA-00959:

> impdp DIRECTORY=EXPDP DUMPFILE=test.dmp LOGFILE=test_imp.log REMAP_TABLESPACE=test_tbs:APP_CAS_TBS_01

generates: ORA-00959: tablespace 'test_tbs' does not exist
Changes

NONE
Cause

DataPump Utility translates the tablespace name to upper case when a parameter file is not used to start the job.
Solution

To have a successful execution of DataPump use any of below alternatives:

1/ Use parameter file to pass lower case tablespace names:

REMAP_SCHEMA=PAWANGREPORT:PAWANGREPORTQA
DIRECTORY=EXPDP
DUMPFILE=APP_CAS.dmp
LOGFILE=APP_CASimp.log
REMAP_TABLESPACE=\"test_tbs\":APP_CAS_TBS_01

- OR -

2/ Rename the tablespace:
SQL> alter tablespace "test_tbs" rename to TEST_TBS;
Tablespace altered.

and restart the DataPump job.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Solved

Naresh, January 08, 2019 - 8:31 am UTC

Thanks a lot for your support and help.

I was able to import the schema using param file from lowercase tablespaces. I will ensure to create upper case tablespace names in future.

Chris Saxon
January 08, 2019 - 11:18 am UTC

Happy to help.

for temporary tablespace

vikas, August 02, 2019 - 5:19 pm UTC

Hi Chris..

Can we change temporary tablespace while import .

source has temporary tablespace TEMP_NEW.
destination has temporary tablespace TEMP.

Please suggest.
Connor McDonald
August 05, 2019 - 9:08 am UTC

Check out the REMAP_TABLESPACE parameter

remap_tablespace support for wilecards

Rajeshwaran Jeyabal, August 08, 2019 - 1:58 pm UTC

we have an application database in Oracle 12c with many schemas having and around 150 Tablespaces.
now planning to move this to a new environment using datapump utilities.
as part of this move planning to consolidate tablespace into a single tablespace (TBS_DATA)

so rather than listing all the existing tablespace name in the remap_tablespace command like this
remap_tablespace = A:TBS_DATA
remap_tablespace = B:TBS_DATA
remap_tablespace = C:TBS_DATA


Can we do something like this? using wilecards?
remap_tablespace = * : TBS_DATA


if not, what would be the easy way to mention those 150+ tablespace name in the remap_tablespace option?
Connor McDonald
August 09, 2019 - 6:51 am UTC

Options

1)

- make the default tablespace for the user TBS_DATA
- import with transform=segment_attributes:n

(but note that this remove storage level attributes as well)

2)

Simply generate the list, ie

select 'remap_tablespace='||tablespace_name||':tbs_data'
from dba_tablespaces
where ...


and add that to your parfile
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.