search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
978 stars 341 forks source link

Tablespace mapping issue from oracle to postgres #1753

Closed priyankadadi closed 3 months ago

priyankadadi commented 3 months ago

We have two databases on the source Oracle server and we are trying to migrate from Oracle to Postgres using ora2pg. The two databases had the same tablespaces in Oracle but when we try to import from Oracle to Postgres for the second database it says tablespace already exists

Question-1: Is it possible to use the same tablespaces in Postgres for two databases?

Question 2: We tried to change the tablespace names for the second database by mapping the Oracle tablespace name with a different Postgres tablespace in ora2pg.conf file but still the export files are generated with Oracle mapped tablespace, not with the mentioned tablespace. Is it possible to migrate with a different tablespace to Postgres?

ora2pg
darold commented 3 months ago

Question-1: Is it possible to use the same tablespaces in Postgres for two databases? This is not the same concept for tablespace between Oracle and PostgreSQL. In PostgreSQL this is just a location on your disk, i-e a folder where the tables files will be stored. Tablespace are global objects that mean that you can use the same tablespace on all database you want.

Question 2: Is it possible to migrate with a different tablespace to Postgres? Not directly with an ora2pg.conf directive but you can always use the sed command to modify the name of the tablespace in the files generated by Ora2Pg.