LightDB通过fdw来支持Oracle的DBLink。
Oracle查询示例:
select * from table_name@dblink_name;
其中dblink_name是用户自定义的dblink名称,table_name为表名。
假设在lightdb中有postgres库和db100库,其中db100库中有表t100:
db100=# \d+ t100
Table "public.t100"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | text | | | | extended | |
Distributed randomly
Access method: heap
db100=# select * from t100;
a | b
------+-------
2000 | abcde
1000 | abcde
(2 rows)
切回postgres库,并对表t100做fdw表映射
create server db100_fdw foreign data wrapper postgres_fdw options(host '192.168.237.145',port '59000',dbname 'db100');
create user mapping for postgres server db100_fdw options(user 'gpadmin', password 'gp123456');
create foreign table db100_fdw_t100(a int, b text) server db100_fdw options(schema_name 'public',table_name 't100');
其中表db100_fdw_t100为db100库中表t100的表映射:
postgres=# select * from db100_fdw_t100;
a | b
------+-------
2000 | abcde
1000 | abcde
(2 rows)
我们可以直接用Oracle的dblink方式来访问表t100:
postgres=# select * from t100@db100_fdw;
a | b
------+-------
2000 | abcde
1000 | abcde
(2 rows)
这样就通过fdw的方式实现了Oracle分dblink跨库访问。