0

I created a dump of a local oracle database like this:

expdp mydb/passwd -schemas=myschema -dumpfile=mydumpfile.dmp -logfile=oralog.log

I sent the dump to someone who is supposed to import the dump in his oracle server. Now, he tells me, the import fails due to some errors related to tablespaces (like tablespace XYZ is not available, - the database XYZ is in no relation to the respective database). Besides, he asks me to give some information about the dump concerning the tablespaces.

Since I am usually working with MySQL and have limited knowledge about these Oracle-Tablespace things: I would really appreciate to get some advise.

Lokomotywa
  • 2,624
  • 8
  • 44
  • 73
  • 1
    The person doing the import needs to do [this](http://stackoverflow.com/a/28296433/266304), but what information do you need? The old tablespace names, which you can get from `all_segments`? – Alex Poole Feb 13 '15 at 12:09
  • @Alex Poole, how do I use `all_segments`? – Lokomotywa Feb 13 '15 at 12:18
  • I think, no need of making it so complex. Do you have the export log? Then you will find all the details. Just provide the `export log` to the person who would import. And tell him to use the `remap_tablespace` parameter. – Lalit Kumar B Feb 13 '15 at 13:24

1 Answers1

3

Use REMAP_TABLESPACE parameter.

For example,

REMAP_TABLESPACE=(source1:destination1,source2:destination1,source3:destination1,source4:destination1)

Go through the documentation about Data Pump Import. A small quote -

Multiple REMAP_TABLESPACE parameters can be specified, but no two can have the same source tablespace. The target schema must have sufficient quota in the target tablespace.

Note that use of the REMAP_TABLESPACE parameter is the only way to remap a tablespace in Data Pump Import. This is a simpler and cleaner method than the one provided in the original Import utility. That method was subject to many restrictions (including the number of tablespace subclauses) which sometimes resulted in the failure of some DDL commands.

By contrast, the Data Pump Import method of using the REMAP_TABLESPACE parameter works for all objects, including the user, and it works regardless of how many tablespace subclauses are in the DDL statement.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Ok, so I repeat my question: How do I "query all_segments"? – Lokomotywa Feb 13 '15 at 14:15
  • 1
    Log into the DB, execute `select * from all_segments`, look out for the tablespace_name. – Lalit Kumar B Feb 13 '15 at 14:23
  • unfortunately, I get a `ORA-00942` – Lokomotywa Feb 13 '15 at 14:26
  • `ORA-00942: table or view does not exist` means you don't have access to the view. Can you try `user_segments` view – Lalit Kumar B Feb 13 '15 at 14:31
  • yes, there are some entries with tablespace_name other than the one with the same name of the database scheme. – Lokomotywa Feb 13 '15 at 14:40
  • Just provide the same information to the other person. Make sure you provide this Stack Overflow link to him so that he/she would have a better idea about the task. Please mark it as answered if you think it answered your question. Folks looking out for similar issue would find it helpful. – Lalit Kumar B Feb 13 '15 at 14:42
  • Ok, but what is the other person doing with that information, that 'select * from user_segments' on the respective database consists of several different tablespaces, namely XYZ, ABC and DEF? – Lokomotywa Feb 13 '15 at 14:44
  • Comments are not for extended discussion; this conversation has been [moved to chat](http://chat.stackoverflow.com/rooms/70858/discussion-on-answer-by-lalit-kumar-b-oracle-export-import-issues-with-tablespac). – Taryn Feb 13 '15 at 14:53
  • The other person, rather the poor guy need to know the tablespace_name. How would you feel if I give you a car to drive but without the keys? – Lalit Kumar B Feb 13 '15 at 14:54
  • In MySQL there is nothing like a tablespace. If I want to transfer a database, I simply send someone a dump. – Lokomotywa Feb 13 '15 at 14:56
  • actually, the name of the additional tablespace is XYZ, which is exactly the one that the poor other person was complaining about. I don't think that giving him back exactly this information would be of any help for him. – Lokomotywa Feb 13 '15 at 14:59
  • if I could remove this weird tablespace, know what it is good for, transfer the information on it to the main tablespace, that would be usefull. – Lokomotywa Feb 13 '15 at 15:01
  • You have exported only a particular schema, which might/would belong to more than one tablespace. Thus, provide the poor guy the details of the tablespaces involved. Anyway, I have had spent much time to help you, I wish you could find your answer out of the information provided to you via answer and the multiple comments. – Lalit Kumar B Feb 13 '15 at 15:03