Modifying object structure
One of the great usage of Ora2Pg is its flexibility to replicate Oracle database into PostgreSQL database with a different structure or schema. There’s three configuration directives that allow you to map those differences.
REORDERING_COLUMNS
Enable this directive to reordering columns and minimized the footprint on disc, so that more rows fit on a data page, which is the most important factor for speed. Default is disabled, that mean the same order than in Oracle tables definition, that’s should be enough for most usage. 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 consist in a space-separated list of table name with a set of column between parenthesis as follow:
MODIFY_STRUCT NOM_TABLE(nomcol1,nomcol2,...) ...
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 key pointing to or from a column removed will not be exported.
REPLACE_TABLES
This directive allows you to remap a list of Oracle table name to a PostgreSQL table name 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 into DEST_TBNAME1 and DEST_TBNAME2
REPLACE_COLS
Like table name, the name of the column can be remapped to a different name 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 into new name ‘dictionary’ and ‘folder’.
