< pg@whf307 /oracle/soft/pg 09:11 --> unzip oracle_fdw-ORACLE_FDW_2_1_0.zip  
< pg@whf307 /oracle/soft/pg 09:03 --> cd oracle_fdw-ORACLE_FDW_2_1_0/

< pg@whf307 /oracle/soft/pg/oracle_fdw-ORACLE_FDW_2_1_0 09:03 --> make
< pg@whf307 /oracle/soft/pg/oracle_fdw-ORACLE_FDW_2_1_0 09:03 --> make install

mydb=# create extension oracle_fdw;

ERROR:  could not load library "/oracle/soft/pg_11/lib/postgresql/oracle_fdw.so": libclntsh.so.11.1: cannot open shared object file: No such file or directory

li=# create database orcl;
CREATE DATABASE
li=# \c orcl
You are now connected to database "orcl" as user "pg".
orcl=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

orcl=# create extension oracle_fdw;
CREATE EXTENSION
orcl=# CREATE EXTENSION ora_migrator;
CREATE EXTENSION
orcl=# \dx
                             List of installed extensions
     Name     | Version |   Schema   |                   Description                   
--------------+---------+------------+-------------------------------------------------
 ora_migrator | 0.9.1   | public     | Tools to migrate Oracle databases to PostgreSQL
 oracle_fdw   | 1.1     | public     | foreign data wrapper for Oracle access
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

orcl=# 

orcl=# create user scott  password 'oracle' superuser;
CREATE ROLE

SQL> create user sa identified by oracle;
User created.

SQL> grant dba to sa;
Grant succeeded.

orcl=#\c orcl scott
You are now connected to database "orcl" as user "scott".

orcl=# \des
       List of foreign servers
 Name | Owner | Foreign-data wrapper 
------+-------+----------------------
(0 rows)

orcl=# create server oracle foreign data wrapper oracle_fdw options (dbserver '//127.0.0.1/wade');
CREATE SERVER
orcl=# \des
        List of foreign servers
  Name  | Owner | Foreign-data wrapper 
--------+-------+----------------------
 oracle | pg    | oracle_fdw
(1 row)

orcl=# create user mapping for scott server oracle options ( user 'sa',password 'oracle');
CREATE USER MAPPING
orcl=# 

SQL> select object_type,count(*) from dba_objects where owner='SC' group by object_type order by 2;

OBJECT_TYPE           COUNT(*)
------------------- ----------
VIEW                         1
MATERIALIZED VIEW            1
FUNCTION                     1
SEQUENCE                     1
PROCEDURE                    2
INDEX                        4
TABLE                        4

7 rows selected.

SQL>  select object_name,object_type from dba_objects where owner='SC';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
T2                             TABLE
T1                             TABLE
PK_T1                          INDEX
PK_T2                          INDEX
IND_T1_NAME                    INDEX
STAT_P4                        PROCEDURE
MV_T1                          TABLE
PK_T11                         INDEX
MV_T1                          MATERIALIZED VIEW
TMP_T1                         TABLE
SP_PROFILER_TEST1              PROCEDURE
SEQTEST                        SEQUENCE
V_T1                           VIEW
GET_ROWID                      FUNCTION

14 rows selected.

SQL> 

orcl=# \i obj.sql
 database | user | schema | reltype | count 
----------+------+--------+---------+-------
(0 rows)

orcl=# 

orcl=# SELECT oracle_migrate(server => 'oracle', only_schemas => '{SC}'); 
NOTICE:  Creating staging schemas "ora_stage" and "pgsql_stage" ...
NOTICE:  Creating Oracle metadata views in schema "ora_stage" ...
NOTICE:  Copy definitions to PostgreSQL staging schema "pgsql_stage" ...
NOTICE:  Creating schemas ...
NOTICE:  Creating sequences ...
NOTICE:  Creating foreign tables ...
NOTICE:  Migrating table sc.t1 ...
NOTICE:  Migrating table sc.t2 ...
WARNING:  Error creating view sc.v_t1
DETAIL:  column "OWNER" does not exist: 
NOTICE:  Creating UNIQUE and PRIMARY KEY constraints ...
NOTICE:  Creating FOREIGN KEY constraints ...
NOTICE:  Creating CHECK constraints ...
NOTICE:  Creating indexes ...
NOTICE:  Setting column default values ...
NOTICE:  Dropping staging schemas ...
NOTICE:  Migration completed with 1 errors.
 oracle_migrate 
----------------
              1
(1 row)


orcl=# \i obj.sql  
 database | user  | schema |    reltype    | count 
----------+-------+--------+---------------+-------
 orcl     | scott | sc     | sequence      |     1
 orcl     | scott | sc     | table         |     2
 orcl     | scott | sc     | index         |     3
(4 rows)

orcl=# 

orcl=# SELECT oracle_migrate(server => 'oracle', only_schemas => '{SC}'); 
WARNING:  Cannot establish connection with foreign server "oracle"
ERROR:  error connecting to Oracle: OCIEnvCreate failed to create environment handle
DETAIL:  
CONTEXT:  SQL statement "SELECT oracle_diag(server)"
PL/pgSQL function public.oracle_migrate_prepare(name,name,name,name[],integer) line 16 at PERFORM
PL/pgSQL function public.oracle_migrate(name,name,name,name[],integer,boolean) line 15 at assignment
orcl=# SELECT oracle_diag();
                         oracle_diag                         
-------------------------------------------------------------
 oracle_fdw 2.1.0, PostgreSQL 11.2, Oracle client 11.2.0.4.0
(1 row)

orcl=# select count(*) from sc.t1;
 count 
-------
  3701
(1 row)

orcl=# \d sc.t1
                                  Table "sc.t1"
     Column     |              Type              | Collation | Nullable | Default 
----------------+--------------------------------+-----------+----------+---------
 owner          | character varying(30)          |           | not null | 
 object_name    | character varying(30)          |           | not null | 
 subobject_name | character varying(30)          |           |          | 
 object_id      | numeric                        |           | not null | 
 data_object_id | numeric                        |           | not null | 
 object_type    | character varying(19)          |           |          | 
 created        | timestamp(0) without time zone |           | not null | 
 last_ddl_time  | timestamp(0) without time zone |           | not null | 
 timestamp      | character varying(19)          |           |          | 
 status         | character varying(7)           |           |          | 
 temporary      | character varying(1)           |           |          | 
 generated      | character varying(1)           |           |          | 
 secondary      | character varying(1)           |           |          | 
 namespace      | numeric                        |           | not null | 
 edition_name   | character varying(30)          |           |          | 
Indexes:
    "t1_pkey" PRIMARY KEY, btree (data_object_id)
    "t1_object_name_idx" btree (object_name)
Check constraints:
    "t1_object_id_check" CHECK (object_id > 0::numeric)
Referenced by:
    TABLE "sc.t2" CONSTRAINT "t2_data_object_id_fkey" FOREIGN KEY (data_object_id) REFERENCES sc.t1(data_object_id)

orcl=# 

SELECT current_database() AS DATABASE,rolname as user,
       nspname AS SCHEMA,
       CASE
           WHEN relkind='r' THEN 'table'
           WHEN relkind='i' THEN 'index'
           WHEN relkind='S' THEN 'sequence'
           WHEN relkind='t' THEN 'TOAST'
           WHEN relkind='v' THEN 'view'
           WHEN relkind='m' THEN 'materialized view'
           WHEN relkind='c' THEN 'composite type'
           WHEN relkind='f' THEN 'foreign table'
           WHEN relkind='p' THEN 'partitioned table'
           WHEN relkind='I' THEN 'partitioned index'
       END AS reltype,
       count(*)
FROM pg_class a,
     pg_namespace b,
     pg_authid c
WHERE a.relnamespace=b.oid
and a.relowner=c.oid
  AND nspname NOT IN ('pg_catalog',
                      'pg_toast',
                      'information_schema')
GROUP BY nspname,rolname,
         relkind
ORDER BY 5,2;