Using Ora2PG migration tool

ora2pg

# homepage: http://ora2pg.darold.net

# pre-requisites
# perl 5.6+
perl -v
# DBI perl module (-i install, -f force)
cpan -i -f DBI
# DBD::Oracle module
cpan -i -f DBD::Oracle

# installation
# download at: http://sourceforge.net/projects/ora2pg/
tar xjf ora2pg-8.7.tar.bz2
cd ora2pg-8.7/
perl Makefile.PL
make && make install



# ora2pg.conf SAMPLE (SCHEMA, TABLE, etc must be in uppercase)
# sid is the database name (check databases at /etc/oratab file)
ORACLE_DSN       dbi:Oracle:host=localhost;sid=orcl
ORACLE_USER	     my_user
ORACLE_PWD	     passwd
# will default to postgres default schema ("public")
EXPORT_SCHEMA    0
# schema is a username (owner)
SCHEMA           SCHEMA_USERNAME_MUST_BE_UPPERCASE
# to export table, but the script suggested below changes it for each type in a for loop
TYPE             TABLE
NLS_LANG         "BRAZILIAN PORTUGUESE_BRAZIL.UTF8
CLIENT_ENCODING  UTF-8


# simple export shell script
for i in TABLE PACKAGE COPY VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE ;\
do \
  perl -p -i -e "s/^TYPE\s+.*/TYPE $i/; s/^OUTPUT\s+.*/OUTPUT $i.sql/" ora2pg.conf; \
  ora2pg --conf ora2pg.conf; \
done