[Oracle] ORA-01950 with REMAP_TABLESPACE – Solved!

Today I’ve been importing an Oracle database to a test server to copy a JIRA instance. I’m doing this after many years, because, at work what I would have normally done is submitting a ticket to DBAs. However, in this case, this is a personal database server I maintain for testing purposes, so I’m on my own. 🙂

I hit a snag when data import tool has successfully imported some objects to the desired tablespace, while attempted to import some other objects to USERS tablespace. This made overall import unsuccessful and at the end it threw a large number of ORA-01950 error log entries.

The command I was using to is something like this (actual names replaced by placeholders):

$ impdp targetuser/targetuser \
directory=DATA_PUMP_DIR \
dumpfile=source.dmp logfile=importlog.txt \
REMAP_TABLESPACE=SOURCE:TARGET \
remap_schema=sourceuser:targetuser

I googled a little about the error and found this solution: “How to remap tablespaces using Oracle Import/Export Tool”

When following their fist workaround to generate SQL using INDEXFILE option and then to manually edit the SQL, I kind of got a hint about the root cause.

Why would you want to find and replace “USERS” tablespace name on the SQL file? What forced “USERS” tablespace to be included in the automatically generated SQL? These were the questions I asked myself.

The root cause could be that original schema (source) could have already had different objects created on both its own tablespace and the USERS tablespace. In that case, all I need to do is just remapping USERS tablespace as well.

This way, I would be correcting the previous mistake as well. So, at the end this one worked:

$ impdp targetuser/targetuser \
directory=DATA_PUMP_DIR \
dumpfile=source.dmp logfile=importlog.txt \
REMAP_TABLESPACE=SOURCE:TARGET REMAP_TABLESPACE=USERS:TARGET \
remap_schema=sourceuser:targetuser

Just repeat remap_tablespace one more time to remap USERS to your preferred target. Thankfully, the article I hyperlinked above gave me the correct indication to find the root cause, although it is not the solution.

All good now! 🙂