How to move an Oracle spatial table to another db?

Question

I have uploaded a polyline data (50 million rows) to oracle spatial 10g as sdo_geometry through arccatalog and arcsde in my TEST database.

Now, I need to move this table to QA database, this table is not having any relationships with other table.

It took nearly 1 day to upload the data to TEST through arcsde. I want to do it using Oracle tools, which is the best method to copy/move this huge data with all informations like index, metadata etc., thanks, vadivelan. P

• Mapperz: Do you have access to FME? or Datapump will work as unicoletti mentions.

Answer 1

Oracle 10 introduced datapump which is a more flexible way of moving data in and out of an oracle database than its predecessor imp/exp.

Datapump supports spatial data, so you can use it and will move metadata, grants, indexes, etc.

To ensure that the move is as smooth as possible make sure that you have the same tablespaces in both databases and that the user doing the import has access to them or you will have to manually relocating them (as shown here) which is slow and error prone.

As for speed: I am afraid that one table with 50m rows will be slow and there is not much you can do about it, except maybe importing it without indexes and recreating them at the end.