Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Dario.

Asked: August 17, 2016 - 1:38 pm UTC

Last updated: August 18, 2016 - 11:08 pm UTC

Version: 12.1.0.1

Viewed 1000+ times

You Asked

Hi Tom,

I was asked to convert EE DB to SE DB on same machine.

I have following doubts regarding creation of tablespaces and datafiles.
1. If I precreate TABLESPACES on SE database before full impdp then import logs error because it tries to create tablespace with same datafile path like in EE.
2. If I precreate TABLESPACES on SE and remap_datafile to new files before full impdp I still get error since files already exists on SE DB
3. If I don't create TABLESPACES and remap_datafiles before full impdp I still get errors for USERS, UNDOTBS1 and TEMP tablespaces which already exists.

How should I specify impdp in a way that:
- there will not be any errors while creating tablespaces
- files from EE DB will not be overwriten because there was no remap_datafile
- SE tablespaces will be same size as EE tablespaces so there will be enough space for all tables
- system.dbf are not overwriten because falsely defined remap_datafile parameter

Br,
Dario

and Connor said...

When doing a full datapump import into a clean database, then you will always get some "errors", eg

RA-31684: Object type TABLESPACE:"SYSAUX" already exists
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
...
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"ORACLE" already exists
ORA-31684: Object type USER:"NORMAN" already exists


because the target database is not empty. This is not a cause for concern. As with all datapump operations, you would review the log file at the end of the task to make sure that any warnings that were logged can be either ignored or acted upon.

Personally...I prefer to precreate tablespaces, because I have full control over the location of the files.

Rating

  (1 rating)

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

Comments

A reader, August 18, 2016 - 9:28 am UTC

Tnx Connor,

What about following situation.
Both DB are on same machine and I'm trying to convert EE DB to SE DB.
- EE TS TEST with datafile '/u01/datafiles/ee/test.dbf'
- SE TS TEST with datafile '/u01/datafiles/se/test.dbf'

If I impdp without specifying:
remap_datafile="'/u01/datafiles/ee/test.dbf':'/u01/datafiles/se/test.dbf'"

Is there possibility that impdp will try to create '/u01/datafiles/ee/test.dbf' for SE DB and in this way overwrite or corrupt datafile from EE?

Br,
Dario
Connor McDonald
August 18, 2016 - 11:08 pm UTC

If the tablespaces already exists, then they will not attempt to be created, and hence you wont overwrite anything.

But it wont hurt to add the remap if that makes you feel secure.
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.