Modifying Structure

Modifying object structure

One of the great uses of Ora2Pg is its flexibility to replicate an Oracle database into a PostgreSQL database with a different structure or schema. There are three configuration directives that allow you to map these differences.

• REORDERING_COLUMNS

  Enable this directive to reorder columns and minimize the footprint on disk, so that more rows fit on a data page, which is the most important factor for speed. Default is disabled, meaning the same order as in Oracle tables definition, which should be enough for most uses. This directive is only used with TABLE export.

• MODIFY_STRUCT

  This directive allows you to limit the columns to extract for a given table. The value consists of a space-separated list of table names with a set of columns between parentheses as follows:

MODIFY_STRUCT   TABLE_NAME(colname1,colname2,...) ...

for example:

MODIFY_STRUCT   T_TEST1(id,dossier) T_TEST2(id,fichier)

This will only extract columns 'id' and 'dossier' from table T_TEST1 and columns 'id' and 'fichier' from the T_TEST2 table. This directive can only be used with TABLE, COPY or INSERT export. With TABLE export create table DDL will respect the new list of columns and all indexes or foreign keys pointing to or from a removed column will not be exported.

• EXCLUDE_COLUMNS

  Instead of redefining the table structure with MODIFY_STRUCT you may want to exclude some columns from the table export. The value consists of a space-separated list of table names with a set of column between parentheses as follows:

EXCLUDE_COLUMNS TABLE_NAME(colname1,colname2,...) ...

for example:

EXCLUDE_COLUMNS T_TEST1(id,dossier) T_TEST2(id,fichier)

This will exclude columns 'id' and 'dossier' from table T_TEST1 and columns 'id' and 'fichier' from the T_TEST2 table from the export. This directive can only be used with TABLE, COPY or INSERT export. With TABLE export create table DDL will respect the new list of columns and all indexes or foreign keys pointing to or from a removed column will not be exported.

• REPLACE_TABLES

  This directive allows you to remap a list of Oracle table names to a PostgreSQL table names during export. The value is a list of space-separated values with the following structure:

REPLACE_TABLES  ORIG_TBNAME1:DEST_TBNAME1 ORIG_TBNAME2:DEST_TBNAME2

Oracle tables ORIG_TBNAME1 and ORIG_TBNAME2 will be respectively renamed to DEST_TBNAME1 and DEST_TBNAME2

• REPLACE_COLS

  Like table names, column names can be remapped to different names using the following syntax:

REPLACE_COLS    ORIG_TBNAME(ORIG_COLNAME1:NEW_COLNAME1,ORIG_COLNAME2:NEW_COLNAME2)

For example:

REPLACE_COLS    T_TEST(dico:dictionary,dossier:folder)

will rename Oracle columns 'dico' and 'dossier' from table T_TEST to new names 'dictionary' and 'folder'.

• REPLACE_AS_BOOLEAN

  If you want to change the type of some Oracle columns to PostgreSQL boolean during the export you can define here a list of tables and columns separated by spaces as follows.

REPLACE_AS_BOOLEAN     TB_NAME1:COL_NAME1 TB_NAME1:COL_NAME2 TB_NAME2:COL_NAME2

The values set in the boolean columns list will be replaced with 't' and 'f' following the default replacement values and those additionally set in directive BOOLEAN_VALUES.

Note that if you have modified the table name with REPLACE_TABLES and/or the column's name, you need to use the name of the original table and/or column.

REPLACE_COLS            TB_NAME1(OLD_COL_NAME1:NEW_COL_NAME1)
REPLACE_AS_BOOLEAN      TB_NAME1:OLD_COL_NAME1

You can also give a type and precision to automatically convert all fields of that type to boolean. For example:

REPLACE_AS_BOOLEAN      NUMBER:1 CHAR:1 TB_NAME1:COL_NAME1 TB_NAME1:COL_NAME2

will also replace any field of type number(1) or char(1) as a boolean in all exported tables.

• BOOLEAN_VALUES

  Use this to add additional definitions of the possible boolean values used in Oracle fields. You must set a space-separated list of TRUE:FALSE values. By default, here are the values recognized by Ora2Pg:

BOOLEAN_VALUES          yes:no y:n 1:0 true:false enabled:disabled

Any values defined here will be added to the default list.

• REPLACE_ZERO_DATE

  When Ora2Pg finds a "zero" date: 0000-00-00 00:00:00 it is replaced by a NULL. This could be a problem if your column is defined with a NOT NULL constraint. If you can not remove the constraint, use this directive to set an arbitrary date that will be used instead. You can also use -INFINITY if you don't want to use a fake date.

• INDEXES_SUFFIX

  Add the given value as suffix to index names. Useful if you have indexes with the same name as tables. For example:

INDEXES_SUFFIX          _idx

will add _idx at the end of all index names. Not very common but helpful.

• INDEXES_RENAMING

  Enable this directive to rename all indexes using tablename_columns_names. Could be very useful for databases that have multiple instances of the same index name or that use the same name as a table, which is not allowed by PostgreSQL. Disabled by default.

• USE_INDEX_OPCLASS

  Operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the corresponding types. The difference from the default operator classes is that the values are compared strictly character by character rather than according to locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the database does not use the standard "C" locale. If enabled with value 1, this will force Ora2Pg to export all indexes defined on varchar2() and char() columns using those operators. If you set it to a value greater than 1, it will only change indexes on columns where the character limit is greater than or equal to this value. For example, set it to 128 to create these kinds of indexes on columns of type varchar2(N) where N >= 128.

• RENAME_PARTITION

  Enable this directive if you want your partition tables to be renamed. Disabled by default. If you have multiple partitioned tables, when exported to PostgreSQL some partitions could have the same name but different parent tables. This is not allowed - table names must be unique. In this case, enable this directive. A partition will be renamed following the rule:

"tablename"\_part"pos"

where "pos" is the partition number. For subpartition this is:

"tablename"\_part"pos"\_subpart"pos"

If this is partition/subpartition default:

"tablename"\_part\_default
"tablename"\_part"pos"\_subpart\_default

• DISABLE_PARTITION

  If you don't want to reproduce the partitioning like in Oracle and want to export all partitioned Oracle data into the main single table in PostgreSQL, enable this directive. Ora2Pg will export all data into the main table name. Default is to use partitioning - Ora2Pg will export data from each partition and import them into the PostgreSQL dedicated partition table.

• PARTITION_BY_REFERENCE

  How to export partition by reference. Possible values are none, duplicate or the number of hash partitions to create. Default is none to not export the partitions by reference.

  Value 'none' means no translation and export of partition by reference like before. Value 'duplicate' will duplicate the referenced column in the partitioned table and apply the same partitioning from the referenced table to the partitioned table. If the value is a number, the table will be partitioned with the HASH method using the value as the modulo. For example, if you set it to 4 it will create 4 HASH partitions.

• DISABLE_UNLOGGED

  By default, Ora2Pg exports Oracle tables with the NOLOGGING attribute as UNLOGGED tables. You may want to fully disable this feature because you will lose all data from unlogged tables in case of a PostgreSQL crash. Set it to 1 to export all tables as normal tables.

• DOUBLE_MAX_VARCHAR

  Increase varchar max character constraints to support PostgreSQL two bytes character encoding when the source database applies the length constraint on characters not bytes. Default disabled.