--用户授权
conn / as sysdba
GRANT dba to hr;
GRANT CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY PROCEDURE TO hr;
GRANT ALTER ANY TABLE, ALTER ANY PROCEDURE TO hr;
GRANT DROP ANY TABLE, DROP ANY VIEW, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO hr;
创建存储过程
CREATE OR REPLACE PROCEDURE sp_generate_report_and_diff_and_highest_paid_emp
-- 定义游标变量
CURSOR c_emp IS
SELECT e.first_name,
e.last_name,
e.job_id,
d.department_name,
e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
BEGIN
-- 打印表头信息
DBMS_OUTPUT.PUT_LINE('EMPLOYEE REPORT');
DBMS_OUTPUT.PUT_LINE('----------------');
-- 创建员工报告表
EXECUTE IMMEDIATE 'CREATE TABLE emp_report AS
SELECT emp.first_name,
emp.last_name,
emp.job_id,
dept.department_name,
emp.salary
FROM employees emp
INNER JOIN departments dept ON emp.department_id = dept.department_id';
-- 创建员工薪资差异表
EXECUTE IMMEDIATE 'CREATE TABLE emp_salary_diff AS
SELECT emp.employee_id,
emp.first_name,
emp.last_name,
emp.job_id,
emp.salary,
(emp.salary - AVG(emp.salary) OVER(PARTITION BY emp.job_id)) AS salary_diff
FROM employees emp';
-- 创建薪水最高员工表
EXECUTE IMMEDIATE 'CREATE TABLE highest_paid_emp AS
SELECT emp.employee_id,
emp.first_name,
emp.last_name,
emp.job_id,
dept.department_name,
emp.salary
FROM employees emp
INNER JOIN departments dept ON emp.department_id = dept.department_id
WHERE emp.salary = (SELECT MAX(salary) FROM employees)';
-- 打印表尾信息
DBMS_OUTPUT.PUT_LINE('----------------');
DBMS_OUTPUT.PUT_LINE('END OF REPORT');
执行存储过程
--forceowner : 导入数据时，强制 ora2pg 将导入 PostgreSQL 的表和序列的拥有者设置为连接 Oracle 数据库时的用户。如果设置为指定的用户名，所有导入的对象属于该用户。默认情况下，对象的拥有者为连接 Pg 数据库的用户。
--cost_unit_value minutes: 成本评估单位，使用分钟数表示。默认值为 5 分钟，表示一个 PostgreSQL 专家迁移所需的时间。如果是第一次迁移，可以设置为 10 分钟。
--dump_as_html : 生成 HTML 格式的迁移报告，只能与 SHOW_REPORT 选项一起使用。默认的报告是一个简单的文本文件。
--audit_user LIST : 设置查询 DBA_AUDIT_TRAIL 表时需要过滤的用户名，多个用户使用逗号分隔。该参数只能用于 SHOW_REPORT 和 QUERY 导出类型。
## 在编译安装了PG的机器上安装ora2pg（本测试oracle和pg在同一台机器Tencent上）
--安装依赖包,perl版本5.10以上
yum install -y gcc perl-DBD-Pg perl perl-devel perl-DBI perl-CPAN bzip2 \
perl-ExtUtils-eBuilder perl-ExtUtils-MakeMaker perl-Time-HiRes perl-tests perf cpan
--安装“DBI”
下载地址：https://metacpan.org/release/DBI
tar -zxvf DBI-1.643.tar.gz
cd DBI-1.643
perl Makefile.PL
make && make install
--安装“DBD::Oracle”
下载地址：https://sourceforge.net/projects/ora2pg/
tar -zxvf DBD-Oracle-1.83.tar.gz
cd DBD-Oracle-1.83
perl Makefile.PL
make && make install
--配置root 环境变量，添加oracle的信息
cat >> /root/.bash_profile << "EOF"
export ORACLE_HOME=/oracle/app/oracle/product/12.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
source /root/.bash_profile
--安装“ora2pg”
下载地址: https://github.com/darold/ora2pg/releases/tag/v23.2
tar -zxvf ora2pg-23.2.tar.gz
cd ora2pg-23.2
perl Makefile.PL
make && make install
--安装“DBD::Pg”（可选）
如果想直接将ora2pg导出的数据直接导入pg数据库，不生成本地文件，则可以安装该插件并配置使用
下载地址：http://www.cpan.org/authors/id/T/TU/TURNSTEP/
tar -zxvf DBD-Pg-3.16.3.tar.gz
cd DBD-Pg-3.16.3
perl Makefile.PL # 输入/postgresql/pg15/bin/pg_config
perl Makefile.PL
make && make install
--查看ora2pg安装情况
[root@tencent tmp]# which ora2pg
/usr/local/bin/ora2pg
[root@tencent tmp]# ora2pg –version
Ora2Pg v23.2
--检查所有软件是否已成功安装
创建检查脚本
cat > /root/check.pl <<"EOF"
#!/usr/bin/perl
use strict;
use ExtUtils::Installed;
my $inst= ExtUtils::Installed->new();
my @modules = $inst->modules();
foreach(@modules)
my $ver = $inst->version($_) || "???";
printf("%-12s -- %s\n", $_, $ver);
exit;
[root@tencent tmp]# perl /root/check.pl
DBD::Oracle -- 1.83
DBD::Pg -- 3.16.3
DBI -- 1.643
Ora2Pg -- 23.2
[root@tencent ora2pg]# ora2pg -t SHOW_REPORT --estimate_cost -c ora2pg_table_ddl.conf
[========================>] 9/9 tables (100.0%) end of scanning.
[========================>] 10/10 objects types (100.0%) end of objects auditing.
Ora2Pg v23.2 - Database Migration Report
Version Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
Schema HR
Size 54.62 MB
Object Number Invalid Estimated cost Comments Details
DATABASE LINK 0 0 0.00 Database links will be exported as SQL/MED PostgreSQL's Foreign Data Wrapper (FDW) extensions using oracle_fdw.
GLOBAL TEMPORARY TABLE 0 0 0.00 Global temporary table are not supported by PostgreSQL and will not be exported. You will have to rewrite some application code to match the PostgreSQL temporary table behavior.
INDEX 19 0 3.00 11 index(es) are concerned by the export, others are automatically generated and will do so on PostgreSQL. Bitmap will be exported as btree_gin index(es). Domain index are exported as b-tree but commented to be edited to mainly use FTS. Cluster, bitmap join and IOT indexes will not be exported at all. Reverse indexes are not exported too, you may use a trigram-based index (see pg_trgm) or a reverse() function based index and search. Use 'varchar_pattern_ops', 'text_pattern_ops' or 'bpchar_pattern_ops' operators in your indexes to improve search with the LIKE operator respectively into varchar, text or char columns. 11 b-tree index(es).
JOB 0 0 0.00 Job are not exported. You may set external cron job with them.
PROCEDURE 5 1 31.50 Total size of procedure code: 2415 bytes. secure_dml: 3. sp_500: 3. sp_create_data: 18.5. add_job_history: 3.
SEQUENCE 4 0 1.00 Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name').
SYNONYM 0 0 0.00 SYNONYMs will be exported as views. SYNONYMs do not exists with PostgreSQL but a common workaround is to use views or set the PostgreSQL search_path in your session to access object outside the current schema.
TABLE 9 0 1.20 2 check constraint(s). Total number of rows: 500215. Top 10 of tables sorted by number of rows:. order_table has 500000 rows. employees has 107 rows. departments has 27 rows. countries has 25 rows. locations has 23 rows. jobs has 19 rows. job_history has 10 rows. regions has 4 rows. demo has 0 rows. Top 10 of largest tables:.
TRIGGER 2 0 5.00 Total size of trigger code: 123 bytes. update_job_history: 3.
VIEW 1 0 1.00 Views are fully supported but can use specific functions.
Total 40 1 42.70 42.70 cost migration units means approximatively 1 person-day(s). The migration unit was set to 5 minute(s)
Migration level : B-5
Migration levels:
A - Migration that might be run automatically
B - Migration with code rewrite and a human-days cost up to 5 days
C - Migration with code rewrite and a human-days cost above 5 days
Technical levels:
1 = trivial: no stored functions and no triggers
2 = easy: no stored functions but with triggers, no manual rewriting
3 = simple: stored functions and/or triggers, no manual rewriting
4 = manual: no stored functions but with triggers or views with code rewriting
5 = difficult: stored functions and/or triggers with code rewriting
Details of cost assessment per function
Function sp_create_data total estimated cost: 18.5
DBMS_ => 5 (cost: 3)
TRUNC => 4 (cost: 0.1)
TEST => 2
SIZE => 1
ADD_MONTHS => 1 (cost: 0.1)
Function sp_500 total estimated cost: 3
TEST => 2
SIZE => 1
Function add_job_history total estimated cost: 3
TEST => 2
SIZE => 1
Function secure_dml total estimated cost: 3
TEST => 2
SIZE => 1
Details of cost assessment per trigger
Trigger update_job_history total estimated cost: 3
TEST => 2
-rw-r--r-- 1 root root 312 4月 21 14:18 PACKAGE_other_ddl_output.sql
-rw-r--r-- 1 root root 3264 4月 21 14:18 PROCEDURE_other_ddl_output.sql
-rw-r--r-- 1 root root 760 4月 21 14:18 TRIGGER_other_ddl_output.sql
-rw-r--r-- 1 root root 313 4月 21 14:18 FUNCTION_other_ddl_output.sql
-rw-r--r-- 1 root root 1047 4月 21 14:18 VIEW_other_ddl_output.sql
-rw-r--r-- 1 root root 341 4月 21 14:18 TYPE_other_ddl_output.sql
-rw-r--r-- 1 root root 312 4月 21 14:18 SYNONYM_other_ddl_output.sql
-rw-r--r-- 1 root root 549 4月 21 14:18 SEQUENCE_other_ddl_output.sql
-rw-r--r-- 1 root root 31 4月 21 14:18 MVIEW_other_ddl_output.sql
-rw-r--r-- 1 root root 312 4月 21 14:18 GRANT_other_ddl_output.sql
2.4.5、导出表数据
[root@tencent ora2pg]# ora2pg -c ora2pg_data.conf
[========================>] 12/12 tables (100.0%) end of scanning.
[========================>] 25/25 rows (100.0%) Table COUNTRIES (25 recs/sec)
[=> ] 25/467 total rows (5.4%) - (1 sec., avg: 25 recs/sec).
[========================>] 27/27 rows (100.0%) Table DEPARTMENTS (27 recs/sec)
[==> ] 52/467 total rows (11.1%) - (1 sec., avg: 52 recs/sec).
[========================>] 107/107 rows (100.0%) Table EMPLOYEES (107 recs/sec)
[========> ] 159/467 total rows (34.0%) - (1 sec., avg: 159 recs/sec).
[========================>] 106/106 rows (100.0%) Table EMP_REPORT (106 recs/sec)
[=============> ] 265/467 total rows (56.7%) - (1 sec., avg: 265 recs/sec).
[========================>] 107/107 rows (100.0%) Table EMP_SALARY_DIFF (107 recs/sec)
[===================> ] 372/467 total rows (79.7%) - (2 sec., avg: 186 recs/sec).
[========================>] 1/1 rows (100.0%) Table HIGHEST_PAID_EMP (1 recs/sec)
[===================> ] 373/467 total rows (79.9%) - (2 sec., avg: 186 recs/sec).
[========================>] 19/19 rows (100.0%) Table JOBS (19 recs/sec)
[====================> ] 392/467 total rows (83.9%) - (2 sec., avg: 196 recs/sec).
[========================>] 10/10 rows (100.0%) Table JOB_HISTORY (10 recs/sec)
[====================> ] 402/467 total rows (86.1%) - (2 sec., avg: 201 recs/sec).
[========================>] 23/23 rows (100.0%) Table LOCATIONS (23 recs/sec)
[=====================> ] 425/467 total rows (91.0%) - (3 sec., avg: 141 recs/sec).
[========================>] 4/4 rows (100.0%) Table REGIONS (4 recs/sec)
[======================> ] 429/467 total rows (91.9%) - (3 sec., avg: 143 recs/sec).
[========================>] 19/19 rows (100.0%) Table T1 (19 recs/sec)
[=======================> ] 448/467 total rows (95.9%) - (3 sec., avg: 149 recs/sec).
[========================>] 19/19 rows (100.0%) Table T2 (19 recs/sec)
[========================>] 467/467 total rows (100.0%) - (4 sec., avg: 116 recs/sec).
[postgres@tencent ~]$ psql
psql (15.2)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
orcl | hr | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | libc |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | libc |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | libc | postgres=CTc/postgres+
| | | | | | | =c/postgres
(4 rows)
postgres=# create user u1 with password 'u1';
CREATE ROLE
postgres=# alter role u1 createrole createdb;
ALTER ROLE
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> create database u1;
CREATE DATABASE
postgres=> \c u1
You are now connected to database "xx" as user "u1".
u1=> create schema u1;
CREATE SCHEMA
u1=> \dn
List of schemas
Name | Owner
--------+----------
public | postgres
u1 | u1
(2 rows)
u1=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
orcl | hr | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | libc |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | libc |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | libc | postgres=CTc/postgres+
| | | | | | | =c/postgres
u1 | u1 | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | libc |
--表、序列
orcl=> \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------------------+----------+-------+-------------+---------------+------------+---------------------------------------------------------------------------------------
hr | countries | table | hr | permanent | heap | 8192 bytes | country table. Contains 25 rows. References with locations table.
hr | departments | table | hr | permanent | heap | 8192 bytes | Departments table that shows details of departments where employees +
| | | | | | | work. Contains 27 rows; references with locations, employees, and job_history tables.
hr | departments_seq | sequence | hr | permanent | | 8192 bytes |
hr | emp_details_view | view | hr | permanent | | 0 bytes |
hr | emp_report | table | hr | permanent | heap | 8192 bytes |
hr | emp_salary_diff | table | hr | permanent | heap | 8192 bytes |
hr | employees | table | hr | permanent | heap | 16 kB | employees table. Contains 107 rows. References with departments, +
| | | | | | | jobs, job_history tables. Contains a self reference.
hr | employees_seq | sequence | hr | permanent | | 8192 bytes |
hr | highest_paid_emp | table | hr | permanent | heap | 8192 bytes |
hr | job_history | table | hr | permanent | heap | 8192 bytes | Table that stores job history of the employees. If an employee +
| | | | | | | changes departments within the job or changes jobs within the department, +
| | | | | | | new rows get inserted into this table with old job information of the +
| | | | | | | employee. Contains a complex primary key: employee_id+start_date. +
| | | | | | | Contains 25 rows. References with jobs, employees, and departments tables.
hr | jobs | table | hr | permanent | heap | 8192 bytes | jobs table with job titles and salary ranges. Contains 19 rows. +
| | | | | | | References with employees and job_history table.
hr | locations | table | hr | permanent | heap | 8192 bytes | Locations table that contains specific address of a specific office, +
| | | | | | | warehouse, and/or production site of a company. Does not store addresses / +
| | | | | | | locations of customers. Contains 23 rows; references with the +
| | | | | | | departments and countries tables.
hr | locations_seq | sequence | hr | permanent | | 8192 bytes |
hr | regions | table | hr | permanent | heap | 8192 bytes |
hr | t1 | table | hr | permanent | heap | 8192 bytes |
hr | t2 | table | hr | permanent | heap | 8192 bytes |
(16 rows)
--表大小
orcl=> select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='hr' order by pg_relation_size(relid) desc;
relname | pg_size_pretty
------------------+----------------
locations | 8192 bytes
t1 | 8192 bytes
regions | 8192 bytes
job_history | 8192 bytes
jobs | 8192 bytes
departments | 8192 bytes
emp_report | 8192 bytes
highest_paid_emp | 8192 bytes
t2 | 8192 bytes
emp_salary_diff | 8192 bytes
countries | 8192 bytes
employees | 0 bytes
(12 rows)
orcl=> \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------------+-------+-------+-------------
hr | countries_pkey | index | hr | countries
hr | departments_pkey | index | hr | departments
hr | dept_location_ix | index | hr | departments
hr | emp_department_ix | index | hr | employees
hr | emp_job_ix | index | hr | employees
hr | emp_manager_ix | index | hr | employees
hr | emp_name_ix | index | hr | employees
hr | employees_email_key | index | hr | employees
hr | employees_pkey | index | hr | employees
hr | jhist_department_ix | index | hr | job_history
hr | jhist_employee_ix | index | hr | job_history
hr | jhist_job_ix | index | hr | job_history
hr | job_history_pkey | index | hr | job_history
hr | jobs_pkey | index | hr | jobs
hr | loc_city_ix | index | hr | locations
hr | loc_country_ix | index | hr | locations
hr | loc_state_province_ix | index | hr | locations
hr | locations_pkey | index | hr | locations
hr | regions_pkey | index | hr | regions
(19 rows)
orcl=> \ds
List of relations
Schema | Name | Type | Owner
--------+-----------------+----------+-------
hr | departments_seq | sequence | hr
hr | employees_seq | sequence | hr
hr | locations_seq | sequence | hr
(3 rows)
(4 rows)
orcl=> \df
List of functions
Schema | Name | Result data type | Argument data types
| Type
--------+--------------------------------------------------+------------------+-------------------------------------------------------------------------------------------------------------------------------
------------------------------------------+------
hr | add_job_history | | IN p_emp_id integer, IN p_start_date timestamp without time zone, IN p_end_date timestamp without time zone, IN p_job_id chara
cter varying, IN p_department_id smallint | proc
hr | secure_dml | |
| proc
hr | sp_generate_report_and_diff_and_highest_paid_emp | |
| proc
hr | trigger_fct_update_job_history | trigger |
| func
(4 rows)
--存储过程
orcl=> select oid,proname,proowner from pg_proc where proowner=16476;
oid | proname | proowner
-------+--------------------------------------------------+----------
16804 | trigger_fct_update_job_history | 16476
16801 | add_job_history | 16476
16802 | secure_dml | 16476
16803 | sp_generate_report_and_diff_and_highest_paid_emp | 16476
(4 rows)
--数据库大小
orcl=> select pg_database_size('orcl');
pg_database_size
------------------
8401711
