I am copying tables using ogr2ogr from Oracle to PostGIS using

 ogr2ogr -f "PostgreSQL" PG:"host=<host> port=5432 user=<user> dbname=<dbname> password=<pwd>" OCI:<USERNAME>/<PASSWORD>@<SID>:<table>

This command fails to copy indexed tables, but works on regular tables.

ERROR 1: ERROR: permission denied for relation spatial_ref_sys when copying <table>@AWS

This might be related to https://github.com/hdknr/note/issues/11

Update: By indexing I mean these commands in Oracle:

INSERT INTO user_sdo_geom_metadata VALUES ('my_table', 'GEOLOC',  MDSYS.SDO_DIM_ARRAY(
  MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.0005),
  MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.0005)),  8307 );

What is the right way to copy an indexed table?

  • I can't imagine any possible reason why an index could have any possible impact on a full-scanning SELECT statement.
    – Vince
    Jul 10 '19 at 23:40
  • @Vince It might be related to github.com/hdknr/note/issues/11
    – Stepan
    Jul 11 '19 at 15:33
  • @Vince when you ran the OGR2OGR statement on your Oracle to PostGIS connection, did you have any issues on your indexed tables, or did they all come across OK? Jul 11 '19 at 15:37
  • @Vince When a table is NOT indexed it is copied correctly. When an index is added with CREATE INDEX (see updated question) it returns an error.
    – Stepan
    Jul 11 '19 at 15:46
  • @Stepan You seem to be focusing on the wrong end of the process. It's not the Oracle side that's in difficulty, but the PostgreSQL side. Try to load a 10-row shapefile or CSV into the PG instance; if that fails, you can isolate your spatial_ref_sys problem.
    – Vince
    Jul 11 '19 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.