Replicating Oracle partitioned tables v7

Suggest edits
Note

You must set this option to the same value for both the publication server and the subscription server.

Note

This feature applies only for subscriptions in an EDB Postgres Advanced Server database. It doesn't apply to subscriptions in a PostgreSQL database.

In Oracle, table partitioning allows you to store table rows in different physical locations (tablespaces) according to a rule defined on the table.

The most common types of Oracle table partitioning are the following:

  • Range partitioning. Ranges of values defined on a column determine the tablespace in which a row is stored.
  • List partitioning. A list of values defined on a column determines the tablespace in which a row is stored.
  • Hash partitioning. An algorithm on a column generates a hash key, which determines the tablespace in which a row is stored.

Limitations for hash partitioning

  • Data is consistent only at the parent table across Oracle and EDB Postgres Advanced Server nodes but not across child partition tables. This is due to the difference in hashing algorithms in Oracle and EDB Postgres Advanced Server that distribute data in the partition.
  • Truncate operation on child partition table: You can truncate child partitions in Oracle using the ALTER command. Replication Server doesn't support the ALTER command when the source database type is Oracle or SQL server. In this case, don't truncate child partition tables outside the Replication Server, leading to data consistency across nodes.
Note

If you're using EDB Postgres Advanced Server, table partitioning using Oracle-compatible table partitioning syntax is an available feature. See the section on table partitioning in the Database compatibility for Oracle developers for information. See Replicating Postgres partitioned tables for information on including Postgres partitioned tables in a replication system. The importPartitionAsTable option described here applies only to table partitioning in an Oracle database.

The importPartitionAsTable option controls what happens when an Oracle partitioned table is part of the publication.

importPartitionAsTable={true | false}

The default value is false.

Depending on the Oracle partitioned table type and the setting of the importPartitionAsTable option, one of the following can occur:

  • A set of inherited tables is created in EDB Postgres Advanced Server to which the Oracle partitioned table is replicated. The rows can be stored in different physical locations.
  • A plain, single table with no inheritance is created in EDB Postgres Advanced Server to which the Oracle partitioned table is replicated. All rows are stored in one physical location.
  • No table is created in EDB Postgres Advanced Server for the Oracle partitioned table, and an error message is written to the Migration Toolkit log file.

When importPartitionAsTable=false (the default setting), the following occurs:

  • A list partitioned table is replicated as a set of inherited EDB Postgres Advanced Server tables.
  • A range partitioned table is replicated as a set of inherited EDB Postgres Advanced Server tables.
  • A hash partitioned table is replicated as a set of inherited EDB Postgres Advanced Server tables.
Note

If subscription tables are created as sets of EDB Postgres Advanced Server inherited tables, then you must also set the enableConstBeforeDataLoad option in the publication server configuration file to true. See Specifying a custom URL for an Oracle JDBC connection for information on the enableConstBeforeDataLoad option.

When importPartitionAsTable=true, the following occurs:

  • A list partitioned table is replicated as a single EDB Postgres Advanced Server table with no inheritance.
  • A range partitioned table is replicated as a single EDB Postgres Advanced Server table with no inheritance.
  • A hash partitioned table is replicated as a single EDB Postgres Advanced Server table with no inheritance.

Setting the importPartitionAsTable option to true allows you to replicate a broader range of Oracle partitioned table types but as normal EDB Postgres Advanced Server tables without simulating partitions by using inheritance.

← Prev

Schema migration options

Next →

Specifying a custom URL for an Oracle JDBC connection