Limiting Objects

Limiting objects to export

You may want to export only a part of an Oracle database. Here is a set of configuration directives that will allow you to control which parts of the database should be exported.

• ALLOW

  This directive allows you to set a list of objects to which the export must be limited, excluding all other objects of the same type of export. The value is a space or comma-separated list of object names to export. You can include valid regex into the list. For example:

ALLOW           EMPLOYEES SALE_.* COUNTRIES .*_GEOM_SEQ

will export objects with names EMPLOYEES, COUNTRIES, all objects beginning with 'SALE_' and all objects with a name ending in '_GEOM_SEQ'. The object depends of the export type. Note that regex will not work with 8i database, you must use the % placeholder instead, Ora2Pg will use the LIKE operator.

This is the way to declare global filters that will be used with the current export type. You can also use extended filters that will be applied to specific objects or only on their related export type. For example:

ora2pg -p -c ora2pg.conf -t TRIGGER -a 'TABLE[employees]'

will limit export of triggers to those defined on table employees. If you want to extract all triggers but not some INSTEAD OF triggers:

ora2pg -c ora2pg.conf -t TRIGGER -e 'VIEW[trg_view_.*]'

Or a more complex form:

ora2pg -p -c ora2pg.conf -t TABLE -a 'TABLE[EMPLOYEES]' \
        -e 'INDEX[emp_.*];CKEY[emp_salary_min]'

This command will export the definition of the employee table but will exclude all indexes beginning with 'emp_' and the CHECK constraint called 'emp_salary_min'.

When exporting partitions you can exclude some partition tables by using

ora2pg -p -c ora2pg.conf -t PARTITION -e 'PARTITION[PART_199.* PART_198.*]'

This will exclude partitioned tables for years 1980 to 1999 from the export but not the main partition table. The trigger will also be adapted to exclude those tables.

With GRANT export you can use this extended form to exclude some users from the export or limit the export to some others:

ora2pg -p -c ora2pg.conf -t GRANT -a 'USER1 USER2'

or

ora2pg -p -c ora2pg.conf -t GRANT -a 'GRANT[USER1 USER2]'

will limit export grants to users USER1 and USER2. But if you don't want to export grants on some functions for these users, for example:

ora2pg -p -c ora2pg.conf -t GRANT -a 'USER1 USER2' -e 'FUNCTION[adm_.*];PROCEDURE[adm_.*]'

Advanced filters may need some learning.

Oracle doesn't allow the use of lookahead expressions so you may want to exclude some objects that match the ALLOW regexp you have defined. For example if you want to export all tables starting with E but not those starting with EXP it is not possible to do that in a single expression. This is why you can start a regular expression with the ! character to exclude objects matching the regexp given just after. Our previous example can be written as follows:

ALLOW   E.* !EXP.*

it will be translated into:

REGEXP_LIKE(..., '^E.*$') AND NOT REGEXP_LIKE(..., '^EXP.*$')

in the object search expression.

• EXCLUDE

  This directive is the opposite of the previous. It allows you to define a space or comma-separated list of object names to exclude from the export. You can include valid regex in the list. For example:

EXCLUDE         EMPLOYEES TMP_.* COUNTRIES

will exclude objects with names EMPLOYEES, COUNTRIES and all tables beginning with 'tmp_'.

For example, you can ban some unwanted functions from export with this directive:

EXCLUDE         write_to_.* send_mail_.*

This example will exclude all functions, procedures or functions in a package with names beginning with those regex. Note that regex will not work with 8i database, you must use the % placeholder instead, Ora2Pg will use the NOT LIKE operator.

See above (directive 'ALLOW') for the extended syntax.

• NO_EXCLUDED_TABLE

  By default, Ora2Pg excludes from export some Oracle "garbage" tables from export that should never be part of an export. This behavior generates a lot of REGEXP_LIKE expressions which slow down the export when looking at tables. To disable this behavior enable this directive, you will have to exclude or clean up later by yourself the unwanted tables. The regexps used to exclude the tables are defined in the array @EXCLUDED_TABLES in lib/Ora2Pg.pm. Note this behavior is independent of the EXCLUDE configuration directive.

• VIEW_AS_TABLE

  Set which views to export as tables. By default none. Value must be a list of view names or regexps separated by space or comma. If the object name is a view and the export type is TABLE, the view will be exported as a create table statement. If export type is COPY or INSERT, the corresponding data will be exported.

  See chapter "Exporting views as PostgreSQL table" for more details.

• MVIEW_AS_TABLE

  Set which materialized views to export as tables. By default none. Value must be a list of materialized view names or regexps separated by space or comma. If the object name is a materialized view and the export type is TABLE, the view will be exported as a create table statement. If export type is COPY or INSERT, the corresponding data will be exported.

• NO_VIEW_ORDERING

  By default, Ora2Pg tries to order views to avoid errors at import time with nested views. With a huge number of views this can take a very long time, you can bypass this ordering by enabling this directive.

• GRANT_OBJECT

  When exporting GRANTs you can specify a comma separated list of objects for which privileges will be exported. Default is export for all objects. Here are the possible values: TABLE, VIEW, MATERIALIZED VIEW, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE BODY, TYPE, SYNONYM, DIRECTORY. Only one object type is allowed at a time. For example set it to TABLE if you just want to export privileges on tables. You can use the -g option to overwrite it.

  When used this directive prevents the export of users unless it is set to USER. In this case only user definitions are exported.

• WHERE

  This directive allows you to specify a WHERE clause filter when dumping the contents of tables. The value is constructed as follows: TABLE_NAME[WHERE_CLAUSE], or if you have only one where clause for all tables just put the where clause as the value. Both are possible too. Here are some examples:

# Global where clause applying to all tables included in the export
WHERE  1=1

# Apply the where clause only on table TABLE_NAME
WHERE  TABLE_NAME[ID1='001']

# Applies two different clauses on tables TABLE_NAME and OTHER_TABLE
# and a generic where clause on DATE_CREATE to all other tables
WHERE  TABLE_NAME[ID1='001' OR ID1='002] DATE_CREATE > '2001-01-01' OTHER_TABLE[NAME='test']

Any WHERE clause not included in a table name bracket clause will be applied to all exported tables including the tables defined in the WHERE clause. These WHERE clauses are very useful if you want to archive some data or only export some recent data.

To be able to quickly test data import it is useful to limit data export to the first thousand tuples of each table. For Oracle define the following clause:

WHERE   ROWNUM < 1000

and for MySQL, use the following:

WHERE   1=1 LIMIT 1,1000

This can also be restricted to some tables' data export.

Command line option -W or --where will override this directive for the global part and per table if the table names are the same.

• TOP_MAX

  This directive is used to limit the number of items shown in the top N lists like the top list of tables per number of rows and the top list of largest tables in megabytes. By default it is set to 10 items.

• LOG_ON_ERROR

  Enable this directive if you want to continue direct data import on error. When Ora2Pg receives an error in the COPY or INSERT statement from PostgreSQL it will log the statement to a file called TABLENAME_error.log in the output directory and continue to next bulk of data. Like this you can try to fix the statement and manually reload the error log file. Default is disabled: abort import on error.

• REPLACE_QUERY

  Sometimes you may want to extract data from an Oracle table but you need a custom query for that. Not just a "SELECT * FROM table" like Ora2Pg does but a more complex query. This directive allows you to overwrite the query used by Ora2Pg to extract data. The format is TABLENAME[SQL_QUERY]. If you have multiple tables to extract by replacing the Ora2Pg query, you can define multiple REPLACE_QUERY lines.

REPLACE_QUERY   EMPLOYEES[SELECT e.id,e.firstname,lastname FROM EMPLOYEES e JOIN EMP_UPDT u ON (e.id=u.id AND u.cdate>'2014-08-01 00:00:00')]