ora2pg安装和使用
庾奇思
官方文档
http://ora2pg.darold.net/
http://ora2pg.darold.net/documentation.html
https://github.com/darold/ora2pg
特性
MySQL和Oracle数据迁移到PostgreSQL使用
- Export full database schema (tables, views, sequences, indexes), with
unique, primary, foreign key and check constraints.
- Export grants/privileges for users and groups.
- Export range/list partitions and sub partitions.
- Export a table selection (by specifying the table names).
- Export Oracle schema to a PostgreSQL 8.4+ schema.
- Export predefined functions, triggers, procedures, packages and
package bodies.
- Export full data or following a WHERE clause.
- Full support of Oracle BLOB object as PG BYTEA.
- Export Oracle views as PG tables.
- Export Oracle user defined types.
- Provide some basic automatic conversion of PLSQL code to PLPGSQL.
- Works on any platform.
- Export Oracle tables as foreign data wrapper tables.
- Export materialized view.
- Show a report of an Oracle database content.
- Migration cost assessment of an Oracle database.
- Migration difficulty level assessment of an Oracle database.
- Migration cost assessment of PL/SQL code from a file.
- Migration cost assessment of Oracle SQL queries stored in a file.
- Generate XML ktr files to be used with Penthalo Data Integrator (Kettle)
- Export Oracle locator and spatial geometries into PostGis.
- Export DBLINK as Oracle FDW.
- Export SYNONYMS as views.
- Export DIRECTORY as external table or directory for external_file extension.
- Full MySQL export just like Oracle database.
- Dispatch a list of SQL orders over multiple PostgreSQL connections
- Perform a diff between Oracle and PostgreSQL database for test purpose.
安装（ubuntu18.04/Redhat7.5）
链接：https://pan.baidu.com/s/1LlRilUM8ygrHFjAI4DjMww
提取码：1qaz
安装说明
- perl要求必须在5.8以上
- 依赖安装
- oracle-instantclient
- DBI
- DBD::Oracle
- DBD::mysql
- DBD::Pg
- Compress::Zlib
- 安装ora2pg
说明：perl包安装有多种方式（cpan，apt，源码编译，下面安装三种方式都有用到。
依赖安装
前提
gcc、make等工具都已经安装。此外下面的包编译安装时，部分包会提示不存在对应的lib库，只要安装对应的包的devel包即可。
安装perl
系统默认已经安装了perl，版本号为5.26
安装DBI
cpan
install DBI
安装oracle-instantclient
#下载 InstantClient（Basic、SDK和Sqlplus的zip包）
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
#解压后放到 /opt/user/lib 目录下
unzip instantclient-basic-linux.x64-19.9.0.0.0dbru.zip
unzip instantclient-sdk-linux.x64-19.9.0.0.0dbru.zip
unzip instantclient-sqlplus-linux.x64-19.9.0.0.0dbru.zip
mkdir /opt/user/lib -p
mv instantclient_19_9 /opt/user/lib
#修改权限并配置环境变量
cd /opt/user/lib
chmod 755 instantclient_19_9/
vi /etc/profile
export PATH=/opt/user/lib/instantclient_19_9/:$PATH
export LD_LIBRARY_PATH=/opt/user/lib/instantclient_19_9/:$LD_LIBRARY_PATH
source /etc/profile
安装DBD::Oracle
#下载软件包
https://metacpan.org/pod/DBD::Oracle
#安装
tar -xzvf DBD-Oracle-1.80.tar.gz
cd DBD-Oracle-1.80
#编译
export PATH=/opt/user/lib/instantclient_19_9/:$PATH
export LD_LIBRARY_PATH=/opt/user/lib/instantclient_19_9/:$LD_LIBRARY_PATH
perl Makefile.PL -l
make
make install
##另一种方法
perl -MCPAN -e shell
install DBD::Oracle
安装DBD::Pg
#类似DBD:Oracle
#前提：已经安装了PostgreSQL
#下载软件包 https://metacpan.org/
#编译安装
tar -xzvf DBD-Pg-3.14.2.tar.gz
cd DBD-Pg-3.14.2
perl Makefile.PL -l
make
make install
##另一种方法
perl -MCPAN -e shell
install DBD::Pg
安装DBD::Mysql
#https://metacpan.org/pod/distribution/DBD-mysql/lib/DBD/mysql/INSTALL.pod
sudo apt-get install libdbd-mysql-perl
#centos
yum install perl-DBD-MySQL
安装Compress::Zlib（可选）
perl -MCPAN -e shell
install Compress::Zlib
安装Ora2pg
#下载软件包
https://github.com/darold/ora2pg/releases
#编译安装
tar -xzvf ora2pg-21.0.tar.gz
cd ora2pg-21.0
perl Makefile.PL -l
make
make install
#简单检查
ora2pg --help
Oracle迁移
导出SQL文件
说明
- ora2pg已经在pro环境安装
- oracle数据库已经在pro环境配置（只安装ORACLE客户端也可以）
- 在root用户下操作
- 如果是用navicat导入的，先删除表 project_struct
- 库中没有存储过程、触发器等，所以配置文件中也没有设置要导出这些内容
执行
mkdir -p /opt/tmp/jtqy
cd /opt/tmp/jtqy
cp /etc/ora2pg/ora2pg.conf.dist ora2pg_oracle.conf
sed -i 's/^ORACLE_HOME/# ORACLE_HOME/g' ora2pg_oracle.conf
sed -i 's/^ORACLE_DSN/# ORACLE_DSN/g' ora2pg_oracle.conf
sed -i 's/^ORACLE_USER/# ORACLE_USER/g' ora2pg_oracle.conf
sed -i 's/^ORACLE_PWD/# ORACLE_PWD/g' ora2pg_oracle.conf
sed -i 's/^TYPE/# TYPE/g' ora2pg_oracle.conf
sed -i 's/^INDEXES_RENAMING/# INDEXES_RENAMING/g' ora2pg_oracle.conf
sed -i 's/^NLS_LANG/# NLS_LANG/g' ora2pg_oracle.conf
sed -i 's/^SCHEMA/# SCHEMA/g' ora2pg_oracle.conf
sed -i 's/^DEFAULT_NUMERIC/# DEFAULT_NUMERIC/g' ora2pg_oracle.conf
sed -i 's/^PG_NUMERIC_TYPE/# PG_NUMERIC_TYPE/g' ora2pg_oracle.conf
sed -i 's/^PG_INTEGER_TYPE/# PG_INTEGER_TYPE/g' ora2pg_oracle.conf
echo "ORACLE_HOME /opt/user/lib/instantclient_19_9" >> ora2pg_oracle.conf
echo "ORACLE_DSN dbi:Oracle:host=localhost;sid=ORCL;port=1521" >> ora2pg_oracle.conf
echo "ORACLE_USER PROJECT" >> ora2pg_oracle.conf
echo "ORACLE_PWD PROJECT" >> ora2pg_oracle.conf
echo "INDEXES_RENAMING 1" >> ora2pg_oracle.conf
echo "TYPE TABLE,INSERT,VIEW,SEQUENCE" >> ora2pg_oracle.conf
echo "NLS_LANG AMERICAN_AMERICA.AL32UTF8" >> ora2pg_oracle.conf
echo "SCHEMA PROJECT" >> ora2pg_oracle.conf
echo "DEFAULT_NUMERIC numeric" >> ora2pg_oracle.conf
echo "PG_NUMERIC_TYPE 1" >> ora2pg_oracle.conf
echo "PG_INTEGER_TYPE 1" >> ora2pg_oracle.conf
#测试配置
ora2pg -t SHOW_VERSION -c ora2pg_oracle.conf
#查看导出汇总情况
ora2pg -b /opt/tmp/jtqy -d -c ora2pg_oracle.conf -t SHOW_REPORT --dump_as_html >> dump_info.html
#导出SQL
ora2pg -b /opt/tmp/jtqy -d -c ora2pg_oracle.conf
--收集/opt/tmp/jtqy目录下*_output.sql
#EXPORT_SCHEMA 1 # 将用户导入到PostgreSQL数据库中 DISABLE_UNLOGGED 1 避免将NOLOGGING属性设为UNLOGGED
#SKIP fkeys ukeys checks #跳过外键 唯一 和检查约束
#TYPE TABLE,VIEW,GRANT,SEQUENCE,TABLESPACE,PROCEDURE,TRIGGER,FUNCTION,PACKAGE,PARTITION,TYPE,MVIEW,QUERY,DBLINK,SYNONYM,DIRECTORY,TEST,TEST_VIEW
修正SQL文件
- 对于视图、存储过程等中的日期计算，ORACLE中+1表示的就是加一天，但是PostgreSQL中要写成
+ interval '1 day'
附录：类型转换记录
| ORACLE类型 | PostgreSQL转换后类型 |
|---|---|
| BLOB | bytea |
| CLOB | text |
| NCLOB | text |
| CHAR(10) | CHAR(10) |
| DATE | timestamp without time zone —oracle的date可以带时分秒的 |
| FLOAT(10) | double precision |
| NCHAR(N) | character(N) --PostgreSQL中可以存储N个中文字符 |
| NVARCHAR2(N) | character varying(N) --PostgreSQL中可以存储N个中文字符 |
| TIMESTAMP(6) | timestamp without time zone |
| VARCHAR2(N) | character varying(N) |
| NUMBER NUMBER(10,0) NUMBER(11,0) … | bigint |
| NUMBER(1,0) NUMBER(2,0) NUMBER(3,0) NUMBER(4,0) | smallint |
| NUMBER(5,2) | real |
| NUMBER(8,2) NUMBER(10,2) … | double precision |
MySQL迁移
修改配置文件
复制样例配置文件并修改如下参数
cp /etc/ora2pg/ora2pg.conf.dist ora2pg_mysql.conf
sed -i 's/^ORACLE_DSN/# ORACLE_DSN/g' ora2pg_mysql.conf
sed -i 's/^ORACLE_USER/# ORACLE_USER/g' ora2pg_mysql.conf
sed -i 's/^ORACLE_PWD/# ORACLE_PWD/g' ora2pg_mysql.conf
sed -i 's/^TYPE/# TYPE/g' ora2pg_mysql.conf
sed -i 's/^INDEXES_RENAMING/# INDEXES_RENAMING/g' ora2pg_mysql.conf
echo "ORACLE_DSN dbi:mysql:database=test;host=localhost;port=3306" >> ora2pg_mysql.conf
echo "ORACLE_USER root" >> ora2pg_mysql.conf
#根据实际情况修改参数值
echo "ORACLE_PWD xxxx" >> ora2pg_mysql.conf
echo "INDEXES_RENAMING 1" >> ora2pg_mysql.conf
echo "TYPE TABLE,INSERT,VIEW,SEQUENCE" >> ora2pg_mysql.conf
## 如果需要导出数据、视图，需要修改配置项TYPE为 TABLE,INSERT,VIEW
导出SQL文件
# 先执行命令查看获取到的MySQL库结构的相关信息（report）
ora2pg -b /opt/tmp/ -d -m -c ora2pg_mysql.conf -t SHOW_REPORT --dump_as_html >> dump_info.html
# 执行命令导出SQL文件（文件名称默认为output.sql）
ora2pg -b /opt/tmp/ -d -m -c ora2pg_mysql.conf
#参数说明 -b指定导出文件所在目录，-d表示大爷debug信息，-m表示导出的数据库类型为mysql，-c指定配置文件
**说明：**如果报DBD::mysql::st execute failed: Access denied;错误，尝试执行grant process on *.* to 'root'@'%';解决。
修正SQL文件
当前修复的主要有如下几个内容：
- offset/desc为postgresql的关键字，如果用作字段名需要加双引号
- pg中的字段名称不能以数字开头
调用示例：python3 amend_mysql2pg_sql.py -i /opt/tmp/output.sql -o /opt/tmp/output_amend.sql
结束，修正后的数据文件直接在postgresql数据库中执行即可。
附录
amend_mysql2pg_sql.py
# -*- encoding: utf-8 -*-
'''
@File : amend_mysql2pg_sql.py
@Time : 2020/11/03 16:10:02
@Author : Shi Guangsheng
@Version : 1.0
@Contact : shiguangsheng_0514@yeah.net
@Desc : 将ora2pg导出的mysql的sql文件进行修正以便能够直接在postgresql中执行
当前修复的主要有如下几个内容：
1. offset/desc为postgresql的关键字，如果用作字段名需要加双引号
2. pg中的字段名称不能以数字开头
'''
# here put the import lib
import argparse
import re
def output_errorinfo(err_list):
print("Incompatible SQL statements:")
for item in err_list:
print(item)
def write_outputinfo(output_list, outputfile):
file = open(outputfile, 'w')
for item in output_list:
file.write(item)
file.write("\n")
file.close()
def amend_line(content):
content = content.strip().lower()
errinfo = None
for keyword in KEYWORDS:
if content.find("."+keyword+" ") != -1 or content.startswith(keyword+" "):
if not errinfo: errinfo = content
content = content.replace(keyword, '"'+keyword+'"')
# content="COMMENT ON COLUMN xxx.476srcid IS E'xxx';"
m = re.match("(.*)\.(\d+\w+)( )(.*)", content)
if m:
errinfo = content
content = m.group(1) + '."' + m.group(2) + '"' + m.group(3) + m.group(4)
# content="476srcid integer,"
m = re.match("^(\d+\w+)( )(.*)", content)
if m:
errinfo = content
content = '"' + m.group(1) + '"' + m.group(2) + m.group(3)
return content, errinfo
"""
调用示例：python3 amend_mysql2pg_sql.py -i /opt/tmp/output.sql -o /opt/tmp/output_amend.sql
"""
KEYWORDS = ['offset', 'desc']
def main(input_file, output_file):
output_list = []
err_list = []
line_no = 0
with open(input_file, 'r') as file:
while True:
line = file.readline()
line_no += 1
if line:
amend_info, error_info = amend_line(line)
if error_info: err_list.append(str(line_no) + ":" + error_info)
if amend_info: output_list.append(amend_info)
else:
break
output_errorinfo(err_list)
write_outputinfo(output_list, output_file)
return 0
parser = argparse.ArgumentParser()
parser.add_argument('-i', '--input')
parser.add_argument('-o', '--output')
args = parser.parse_args()
if __name__ == '__main__':
input_file = args.input
outputfile = args.output
main(input_file, outputfile)
官方文档学习笔记
帮助文档
Usage: ora2pg [-dhpqv --estimate_cost --dump_as_html] [--option value]
#关键参数
-b | --basedir dir: Set the default output directory, where files
resulting from exports will be stored.
-c | --conf file : Set an alternate configuration file other than the
default /etc/ora2pg/ora2pg.conf.
-D | --data_type STR : Allow custom type replacement at command line.
-i | --input file : File containing Oracle PL/SQL code to convert with
no Oracle database connection initiated.
-L | --limit num : Number of tuples extracted from Oracle and stored in
memory before writing, default: 10000.
-m | --mysql : Export a MySQL database instead of an Oracle schema.
-n | --namespace schema : Set the Oracle schema to extract from.
-o | --out file : Set the path to the output file where SQL will
be written. Default: output.sql in running directory.
-p | --plsql : Enable PLSQL to PLPGSQL code conversion.
-P | --parallel num: Number of parallel tables to extract at the same time.
-s | --source DSN : Allow to set the Oracle DBI datasource.
-u | --user name : Set the Oracle database connection user.
ORA2PG_USER environment variable can be used instead.
-w | --password pwd : Set the password of the Oracle database user.
ORA2PG_PASSWD environment variable can be used instead.
--pg_dsn DSN : Set the datasource to PostgreSQL for direct import.
--pg_user name : Set the PostgreSQL user to use.
--pg_pwd password : Set the PostgreSQL password to use.
ora2pg will return 0 on success, 1 on error. It will return 2 when a child
process has been interrupted and you've gotten the warning message:
"WARNING: an error occurs during data export. Please check what's happen."
Most of the time this is an OOM issue, first try reducing DATA_LIMIT value.
#其他参数
-a | --allow str : Comma separated list of objects to allow from export.
Can be used with SHOW_COLUMN too.
-d | --debug : Enable verbose output.
-e | --exclude str: Comma separated list of objects to exclude from export.
Can be used with SHOW_COLUMN too.
-h | --help : Print this short help.
-g | --grant_object type : Extract privilege from the given object type.
See possible values with GRANT_OBJECT configuration.
-l | --log file : Set a log file. Default is stdout.
-j | --jobs num : Number of parallel process to send data to PostgreSQL.
-J | --copies num : Number of parallel connections to extract data from Oracle.
-N | --pg_schema schema : Set PostgreSQL's search_path.
-q | --quiet : Disable progress bar.
-r | --relative : use \ir instead of \i in the psql scripts generated.
-t | --type export: Set the export type. It will override the one
given in the configuration file (TYPE).
-T | --temp_dir DIR: Set a distinct temporary directory when two
or more ora2pg are run in parallel.
-v | --version : Show Ora2Pg Version and exit.
--estimate_cost : Activate the migration cost evaluation with SHOW_REPORT
--cost_unit_value minutes: Number of minutes for a cost evaluation unit.
default: 5 minutes, corresponds to a migration conducted by a
PostgreSQL expert. Set it to 10 if this is your first migration.
--forceowner : Force ora2pg to set tables and sequences owner like in
Oracle database. If the value is set to a username this one
will be used as the objects owner. By default it's the user
used to connect to the Pg database that will be the owner.
--nls_lang code: Set the Oracle NLS_LANG client encoding.
--client_encoding code: Set the PostgreSQL client encoding.
--view_as_table str: Comma separated list of views to export as table.
--dump_as_html : Force ora2pg to dump report in HTML, used only with
SHOW_REPORT. Default is to dump report as simple text.
--dump_as_csv : As above but force ora2pg to dump report in CSV.
--dump_as_sheet : Report migration assessment with one CSV line per database.
--init_project NAME: Initialise a typical ora2pg project tree. Top directory
will be created under project base dir.
--project_base DIR : Define the base dir for ora2pg project trees. Default
is current directory.
--print_header : Used with --dump_as_sheet to print the CSV header
especially for the first run of ora2pg.
--human_days_limit num : Set the number of human-days limit where the migration
assessment level switch from B to C. Default is set to
5 human-days.
--audit_user LIST : Comma separated list of usernames to filter queries in
the DBA_AUDIT_TRAIL table. Used only with SHOW_REPORT
and QUERY export type.
--count_rows : Force ora2pg to perform a real row count in TEST action.
--no_header : Do not append Ora2Pg header to output file
--oracle_speed : Use to know at which speed Oracle is able to send
data. No data will be processed or written.
--ora2pg_speed : Use to know at which speed Ora2Pg is able to send
transformed data. Nothing will be written.
CONF文件
文件样例：ora2pg.conf.dist
http://ora2pg.darold.net/documentation.html#CONFIGURATION
一些参数说明：
连接配置相关
ORACLE_HOME /usr/local/oracle/10g
ORACLE_DSN dbi:Oracle:host=mydb.mydom.fr;sid=SIDNAME;port=1521
ORACLE_USER system
ORACLE_PWD manager
#PG_DSN dbi:Pg:dbname=test_db;host=localhost;port=5432
#PG_USER test
#PG_PWD test
兼容类参数
# Enable this directive if you have tables or column names that are a reserved
# word for PostgreSQL. Ora2Pg will double quote the name of the object.
USE_RESERVED_WORDS 0 #有bug，comment里的没有加双引号
# Add the given value as suffix to index names. Useful if you have indexes
# with same name as tables. Not so common but it can help.
#INDEXES_SUFFIX _idx
# Enable this directive to rename all indexes using tablename_columns_names.
# Could be very useful for database that have multiple time the same index name
# or that use the same name than a table, which is not allowed by PostgreSQL
# Disabled by default.
INDEXES_RENAMING 0
导出类型
#------------------------------------------------------------------------------
# EXPORT SECTION (Export type and filters)
#------------------------------------------------------------------------------
# Type of export. Values can be the following keyword:
# TABLE Export tables, constraints, indexes, ...
# PACKAGE Export packages
# INSERT Export data from table as INSERT statement
# COPY Export data from table as COPY statement
# VIEW Export views
# GRANT Export grants
# SEQUENCE Export sequences
# TRIGGER Export triggers
# FUNCTION Export functions
# PROCEDURE Export procedures
# TABLESPACE Export tablespace (PostgreSQL >= 8 only)
# TYPE Export user defined Oracle types
# PARTITION Export range or list partition (PostgreSQL >= v8.4)
# FDW Export table as foreign data wrapper tables
# MVIEW Export materialized view as snapshot refresh view
# QUERY Convert Oracle SQL queries from a file.
# KETTLE Generate XML ktr template files to be used by Kettle.
# DBLINK Generate oracle foreign data wrapper server to use as dblink.
# SYNONYM Export Oracle's synonyms as views on other schema's objects.
# DIRECTORY Export Oracle's directories as external_file extension objects.
# LOAD Dispatch a list of queries over multiple PostgreSQl connections.
# TEST perform a diff between Oracle and PostgreSQL database.
# TEST_VIEW perform a count on both side of rows returned by views
# SHOW_VERSION : display Oracle version
# SHOW_SCHEMA : display the list of schema available in the database.
# SHOW_TABLE : display the list of tables available.
# SHOW_COLUMN : display the list of tables columns available and the
# Ora2PG conversion type from Oracle to PostgreSQL that will be
# applied. It will also warn you if there's PostgreSQL reserved
# words in Oracle object names.
# SHOW_REPORT : show a detailed report of the Oracle database content.
TYPE TABLE
# Set which object to export from. By default Ora2Pg export all objects.
# Value must be a list of object name or regex separated by space. Note
# that regex will not works with 8i database, use % placeholder instead
# Ora2Pg will use the LIKE operator. There is also some extended use of
# this directive, see chapter "Limiting object to export" in documentation.
#ALLOW TABLE_TEST
# Set which object to exclude from export process. By default none. Value
# must be a list of object name or regexp separated by space. Note that regex
# will not works with 8i database, use % placeholder instead Ora2Pg will use
# the NOT LIKE operator. There is also some extended use of this directive,
# see chapter "Limiting object to export" in documentation.
#EXCLUDE OTHER_TABLES
导出文件配置
# Allow object constraints to be saved in a separate file during schema export.
# The file will be named CONSTRAINTS_OUTPUT. Where OUTPUT is the value of the
# corresponding configuration directive. You can use .gz xor .bz2 extension to
# enable compression. Default is to save all data in the OUTPUT file. This
# directive is usable only with TABLE export type.
FILE_PER_CONSTRAINT 0
# Allow indexes to be saved in a separate file during schema export. The file
# will be named INDEXES_OUTPUT. Where OUTPUT is the value of the corresponding
# configuration directive. You can use the .gz, .xor, or .bz2 file extension to
# enable compression. Default is to save all data in the OUTPUT file. This
# directive is usable only with TABLE or TABLESPACE export type. With the
# TABLESPACE export, it is used to write "ALTER INDEX ... TABLESPACE ..." into
# a separate file named TBSP_INDEXES_OUTPUT that can be loaded at end of the
# migration after the indexes creation to move the indexes.
FILE_PER_INDEX 0
# Allow foreign key declaration to be saved in a separate file during
# schema export. By default foreign keys are exported into the main
# output file or in the CONSTRAINT_output.sql file. When enabled foreign
# keys will be exported into a file named FKEYS_output.sql
FILE_PER_FKEYS 0
# Allow data export to be saved in one file per table/view. The files
# will be named as tablename_OUTPUT. Where OUTPUT is the value of the
# corresponding configuration directive. You can use .gz xor .bz2
# extension to enable compression. Default is to save all data in one
# file. This is usable only during INSERT or COPY export type.
FILE_PER_TABLE 0
# Allow function export to be saved in one file per function/procedure.
# The files will be named as funcname_OUTPUT. Where OUTPUT is the value
# of the corresponding configuration directive. You can use .gz xor .bz2
# extension to enable compression. Default is to save all data in one
# file. It is usable during FUNCTION, PROCEDURE, TRIGGER and PACKAGE
# export type.
FILE_PER_FUNCTION 0
其他一些有用的参数
# Set this to 1 if you don't want to export comments associated to tables and
# column definitions. Default is enabled.
DISABLE_COMMENT 0
# Use this directive to add a specific schema to the search path to look
# for PostGis functions.
#POSTGIS_SCHEMA
# By default PostgreSQL client encoding is automatically set to UTF8 to avoid
# encoding issue. If you have changed the value of NLS_LANG you might have to
# change the encoding of the PostgreSQL client.
#CLIENT_ENCODING UTF8
# When exporting tables, Ora2Pg normally exports constraints as they are;
# if they are non-deferrable they are exported as non-deferrable.
# However, non-deferrable constraints will probably cause problems when
# attempting to import data to PostgreSQL. The following option set to 1
# will cause all foreign key constraints to be exported as deferrable
FKEY_DEFERRABLE 0
# If deferring foreign keys is not possible du to the amount of data in a
# single transaction, you've not exported foreign keys as deferrable or you
# are using direct import to PostgreSQL, you can use the DROP_FKEY directive.
# It will drop all foreign keys before all data import and recreate them at
# the end of the import.
DROP_FKEY
# Disables alter of sequences on all tables in COPY or INSERT mode.
# Set to 1 if you want to disable update of sequence during data migration.
DISABLE_SEQUENCE 0
# Disables triggers on all tables in COPY or INSERT mode. Available modes
# are USER (user defined triggers) and ALL (includes RI system
# triggers). Default is 0 do not add SQL statement to disable trigger.
# If you want to disable triggers during data migration, set the value to
# USER if your are connected as non superuser and ALL if you are connected
# as PostgreSQL superuser. A value of 1 is equal to USER.
DISABLE_TRIGGERS 0
数据类型转换相关
# If you're experiencing problems in data type export, the following directive
# will help you to redefine data type translation used in Ora2pg. The syntax is
# a comma separated list of "Oracle datatype:Postgresql data type". Here are the
# data type that can be redefined and their default value. If you want to
# replace a type with a precision and scale you need to escape the coma with
# a backslash. For example, if you want to replace all NUMBER(*,0) into bigint
# instead of numeric(38)add the following:
# DATA_TYPE NUMBER(*\,0):bigint
# Here is the default replacement for all Oracle's types. You don't have to
# recopy all type conversion but just the one you want to rewrite.
#DATA_TYPE DATE:timestamp,LONG:text,LONG RAW:bytea,CLOB:text,NCLOB:text,BLOB:bytea,BFILE:bytea,RAW:bytea,UROWID:oid,ROWID:oid,FLOAT:double precision,DEC:decimal,DECIMAL:decimal,DOUBLE PRECISION:double precision,INT:numeric,INTEGER:numeric,REAL:real,SMALLINT:smallint,BINARY_FLOAT:double precision,BINARY_DOUBLE:double precision,TIMESTAMP:timestamp,XMLTYPE:xml,BINARY_INTEGER:integer,PLS_INTEGER:integer,TIMESTAMP WITH TIME ZONE:timestamp with time zone,TIMESTAMP WITH LOCAL TIME ZONE:timestamp with time zone
# If set to 1 replace portable numeric type into PostgreSQL internal type.
# Oracle data type NUMBER(p,s) is approximatively converted to real and
# float PostgreSQL data type. If you have monetary fields or don't want
# rounding issues with the extra decimals you should preserve the same
# numeric(p,s) PostgreSQL data type. Do that only if you need very good
# precision because using numeric(p,s) is slower than using real or double.
PG_NUMERIC_TYPE 1
# If set to 1 replace portable numeric type into PostgreSQL internal type.
# Oracle data type NUMBER(p) or NUMBER are converted to smallint, integer
# or bigint PostgreSQL data type following the length of the precision. If
# NUMBER without precision are set to DEFAULT_NUMERIC (see bellow).
PG_INTEGER_TYPE 1
# NUMBER() without precision are converted by default to bigint only if
# PG_INTEGER_TYPE is true. You can overwrite this value to any PG type,
# like integer or float.
DEFAULT_NUMERIC bigint
# Set it to 0 if you don't want to export milliseconds from Oracle timestamp
# columns. Timestamp will be formated with to_char(..., 'YYYY-MM-DD HH24:MI:SS')
# Enabling this directive, the default, format is 'YYYY-MM-DD HH24:MI:SS.FF'.
ENABLE_MICROSECOND 1
# If you want to replace some columns as PostgreSQL boolean define here a list
# of tables and column separated by space as follows. You can also give a type
# and a precision to automatically convert all fields of that type as a boolean.
# For example: NUMBER:1 or CHAR:1 will replace any field of type number(1) or
# char(1) as a boolean in all exported tables.
#REPLACE_AS_BOOLEAN TB_NAME1:COL_NAME1 TB_NAME1:COL_NAME2 TB_NAME2:COL_NAME2
# Use this to add additional definitions of the possible boolean values in Oracle
# field. You must set a space separated list of TRUE:FALSE values. BY default:
#BOOLEAN_VALUES yes:no y:n 1:0 true:false enabled:disabled
# When Ora2Pg find 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 NOT NULL constraint.
# If you can not remove the constraint, use this directive to set an arbitral
# date that will be used instead. You can also use -INFINITY if you don't want
# to use a fake date.
#REPLACE_ZERO_DATE 1970-01-01 00:00:00
# Some time you need to force the destination type, for example a column
# exported as timestamp by Ora2Pg can be forced into type date. Value is
# a comma-separated list of TABLE:COLUMN:TYPE structure. If you need to use
# comma or space inside type definition you will have to backslash them.
#
# MODIFY_TYPE TABLE1:COL3:varchar,TABLE1:COL4:decimal(9,6)
#
# Type of table1.col3 will be replaced by a varchar and table1.col4 by
# a decimal with precision.
#
# If the column's type is a user defined type Ora2Pg will autodetect the
# composite type and will export its data using ROW(). Some Oracle user
# defined types are just array of a native type, in this case you may want
# to transform this column in simple array of a PostgreSQL native type.
# To do so, just redefine the destination type as wanted and Ora2Pg will
# also transform the data as an array. For example, with the following
# definition in Oracle:
#
# CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15);
# CREATE TABLE club (Name VARCHAR2(10),
# Address VARCHAR2(20),
# City VARCHAR2(20),
# Phone VARCHAR2(8),
# Members mem_type
# );
#
# custom type "mem_type" is just a string array and can be translated into
# the following in PostgreSQL:
#
# CREATE TABLE club (
# name varchar(10),
# address varchar(20),
# city varchar(20),
# phone varchar(8),
# members text[]
# ) ;
#
# To do so, just use the directive as follow:
#
# MODIFY_TYPE CLUB:MEMBERS:text[]
#
# Ora2Pg will take care to transform all data of this column in the correct
# format. Only arrays of characters and numerics types are supported.
#MODIFY_TYPE
# By default Oracle call to function TO_NUMBER will be translated as a cast
# into numeric. For example, TO_NUMBER('10.1234') is converted into PostgreSQL
# call to_number('10.1234')::numeric. If you want you can cast the call to integer
# or bigint by changing the value of the configuration directive. If you need
# better control of the format, just set it as value, for example:
# TO_NUMBER_CONVERSION 99999999999999999999.9999999999
# will convert the code above as:
# TO_NUMBER('10.1234', '99999999999999999999.9999999999')
# Any value of the directive that it is not numeric, integer or bigint will
# be taken as a mask format. If set to none, no conversion will be done.
TO_NUMBER_CONVERSION numeric
类似资料：