Replicating Oracle partitioned tables v7
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 theALTER
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.
- On this page
- Limitations for hash partitioning