Shulou(Shulou.com)06/01 Report--

This article only describes how to export data from Oracle to PostgreSQL using Ora2Pg, but you need to install the prerequisite software DBD::Oracle, DBI, and Ora2Pg before doing so.

Installation reference: install DBD::Oracle, DBI and Ora2Pg under Linux

Installing Ora2Pg will generate a configuration file for using Ora2Pg in the ora2pg directory in the / etc directory.

1. Create a test user and a test table on Oracle

Sys@ORCL > create user zhaoxu identified by zhaoxu;User created.sys@ORCL > grant dba to zhaoxu;Grant succeeded.sys@ORCL > conn zhaoxu/zhaoxu;Connected.zhaoxu@ORCL > create table emp as select * from scott.emp;Table created.zhaoxu@ORCL > select * from emp EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-- -7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 1981 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 1014 rows selected.

2. Modify the parameter file

[oracle@rhel6 ora2pg] $cp / etc/ora2pg/ora2pg.conf / home/oracle/ora2pg/ [oracle@rhel6 ora2pg] $cd / home/oracle/ora2pg/ [oracle@rhel6 ora2pg] $vi ora2pg.conf [oracle@rhel6 ora2pg] $cat ora2pg.conf ORACLE_HOME / u02/app/oracle/product/11.2.4/db1ORACLE_DSN dbi:Oracle:host=192.168.56.2 Sid=orclORACLE_USER zhaoxuORACLE_PWD zhaoxuSCHEMA zhaoxuUSER_GRANTS 0DEBUG 0ORA_INITIAL_COMMANDEXPORT_SCHEMA 0CREATE_SCHEMA 1COMPILE_SCHEMA 0TYPE TABLE,INSERTOUTPUT output.sql

3. Export data using the parameters modified above

[oracle@rhel6 ora2pg] $ora2pg-c ora2pg.conf [= = >] 1 tables (100.0%) end of scanning. 0 tables (0. 0%) end of scanning. [= >] 1 tables (100.0%) end of table export. [= >] 14 total rows 1 rows (1400.0%) Table EMP (14 recs/sec) [= >] 14 total rows (1400.0%)-(0 sec., avg: 14 recs/sec). [= >] 1 rows (100.0%) on total estimated data (1 sec., avg: 1 recs/sec) [oracle@rhel6 ora2pg] $cat output.sql-Generated by Ora2Pg, the Oracle database Schema converter Version 17.6b Copyright 2000-2016 Gilles DAROLD. All rights reserved.-- DATASOURCE: dbi:Oracle:host=192.168.56.2;sid=orclSET client_encoding TO 'UTF8';\ set ON_ERROR_STOP ONCREATE TABLE emp (empno smallint, ename varchar (10), job varchar (9), mgr smallint, hiredate timestamp, sal decimal (7), comm decimal (7), deptno smallint) -- Generated by Ora2Pg, the Oracle database Schema converter, version 17.6b-Copyright 2000-2016 Gilles DAROLD. All rights reserved.-- DATASOURCE: dbi:Oracle:host=192.168.56.2;sid=orclSET client_encoding TO 'UTF8';\ set ON_ERROR_STOP ONBEGIN;INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7369) INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499 precepts) VALUES (7499 precepts); INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7521) VALUES (7521) INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7566 empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7566 Magna 7839); INSERT INTO emp (Mar) VALUES (7654) VALUES (7654 MARTING) INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7698 Magnum Magna Magnum 7839); INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7782) MANAGERN MANAGERTHER MANAGERTHI 1981-06-09 0000MANAGERTO MANAGERI 10) INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7788 precepts) VALUES (7788 precepts) VALUES (7888) VALUES (7839) VALUES (7839) INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7844 INSERT INTO emp) VALUES (7844 precepts); INSERT INTO emp (ADAMS) VALUES (7876) VALUES (7876) VALUES (7788) INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7900MagneJAMES798); INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7902) VALUES (7902). INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7934 MILLERECHERGRAPHY) COMMIT (1982-01-23 00 LV 00L L 10)

4. Transfer the generated output.sql to the PostgreSQL server

[oracle@rhel6 ora2pg] $scp output.sql pguser@192.168.56.25:/home/pguser/pguser@192.168.56.25's password: output.sql 100% 2599 2.5KB/s 00:00

5. Create the corresponding database, user and Schema on the PostgreSQL database

# create a database zhaoxupostgres=# create database zhaoxu CREATE DATABASEpostgres=#\ l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges-+- -postgres | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | = c/pguser + | pguser=CTc/pguser template1 | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | = c/pguser + | | pguser=CTc/pguser zhaoxu | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | zx | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (5 rows) # create user zhaoxu postgres=# create user zhaoxu superuser | CREATE ROLEpostgres=#\ dg List of roles Role name | Attributes | Member of-+-- -+-lx | Superuser Cannot login | {} pguser | Superuser, Create role, Create DB, Replication, Bypass RLS | {} sq | Superuser, Create role Create DB | {} zhaoxu | Superuser | {} zx | Superuser | {} # create Schema zhaoxupostgres=#\ c zhaoxu zhaoxuYou are now connected to database "zhaoxu" as user "zhaoxu" under the zhaoxu library. Zhaoxu = # create schema zhaoxu CREATE SCHEMAzhaoxu=#\ dn List of schemas Name | Owner-+-public | pguser zhaoxu | zhaoxu (2 rows)

6. Use output.sql to import data exported from Oracle

[pguser@rhel7 ~] $psql zhaoxu zhaoxu < output.sql SETCREATE TABLESETBEGININSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1COMMIT [pguser@rhel7 ~] $psql zhaoxu zhaoxu psql (9.6.1) Type "help" for help.zhaoxu=#\ d List of relations Schema | Name | Type | Owner-+- Zhaoxu | emp | table | zhaoxu (1 row) zhaoxu=# select * from emp Empno | ename | job | mgr | hiredate | sal | comm | deptno-+-7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | | 20 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 7566 | JONES | MANAGER | 7839 | 2975.00 | 207654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 7698 | BLAKE | 7839 | 1981-05-01 00:00:00 | 2850.00 | | 30 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | 107788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | | | 20 7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000.00 | | 10 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 30 7876 | ADAMS | 7788 | 1987-05-23 00:00:00 | 1100.00 | 20 7900 | JAMES | CLERK | 7698 | 7698-12-03 00:00: 00 | 950.00 | | 30 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | | 20 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | | 10 (14 rows)

Data import completed.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.