Halo数据库跨库查询——DBlink创建及使用

-- 创建DATABASE、USER、SCHEMACREATE DATABASE dblink;CREATE USER dblink SUPERUSER PASSWORD '123456';CREATE SCHEMA dblink;-- 切换连接的库与用户 \c dblink dblink-- 创建测试表CREATE TABLE test_table (    id INT,    name VARCHAR(50),    address VARCHAR(100),    age INT,    city VARCHAR(50));-- 插入10条测试数据INSERT INTO test_table (id,name, address, age, city) VALUES     (1,'John Doe', '123 Main St', 25, 'New York'),    (2,'Jane Smith', '456 Elm St', 30, 'Los Angeles'),    (3,'Michael Johnson', '789 Oak St', 40, 'Chicago'),    (4,'Emily Davis', '321 Pine St', 35, 'San Francisco'),    (5,'Sophia Lee', '579 Spruce St', 29, 'Dallas'),    (6,'David Wilson', '654 Maple St', 28, 'Boston'),    (7,'Sarah Thompson', '987 Cedar St', 32, 'Seattle'),    (8,'Daniel Anderson', '135 Walnut St', 38, 'Houston'),    (9,'Olivia Martinez', '864 Cherry St', 27, 'Miami'),    (10,'James Taylor', '246 Birch St', 33, 'Denver');-- 查询插入的数据SELECT * FROM test_table;

psql -d oracle-- 创建dblink扩展CREATE EXTENSION dblink;

CREATE SERVER link_dblinkFOREIGN DATA WRAPPER dblink_fdwOPTIONS (dbname 'dblink', host '192.168.87.128', port '1921');

CREATE USER MAPPING FOR haloSERVER link_dblinkOPTIONS (user 'dblink', password '123456');

SELECT * FROM dblink('link_dblink', 'SELECT * FROM test_table') AS t(    id INT,    name VARCHAR(50),    address VARCHAR(100),    age INT,    city VARCHAR(50));

CREATE EXTENSION postgres_fdw;

CREATE SERVER pgfdw_foreign_serverFOREIGN DATA WRAPPER postgres_fdwOPTIONS (host '192.168.87.128', port '1921', dbname 'dblink');

CREATE USER MAPPING FOR haloSERVER pgfdw_foreign_serverOPTIONS (user 'dblink', password '123456');

CREATE FOREIGN TABLE pgfdw_foreign_table (    id INT,    name VARCHAR(50),    address VARCHAR(100),    age INT,    city VARCHAR(50)) SERVER pgfdw_foreign_serverOPTIONS (schema_name 'public', table_name 'test_table');

SELECT * FROM pgfdw_foreign_table@pgfdw_foreign_server;

--创建测试用户CREATE USER dblink  IDENTIFIED BY dblink  DEFAULT TABLESPACE users  TEMPORARY TABLESPACE temp;GRANT CREATE SESSION, DBA TO dblink;sqlplus dblink/dblink@ORCL-- 创建测试表CREATE TABLE test_table (    id INT,    name VARCHAR(50),    address VARCHAR(100),    age INT,    city VARCHAR(50));-- 插入10条测试数据INSERT INTO test_table (id, name, address, age, city) VALUES (1, 'John Doe', '123 Main St', 25, 'New York');INSERT INTO test_table (id, name, address, age, city) VALUES (2, 'Jane Smith', '456 Elm St', 30, 'Los Angeles');INSERT INTO test_table (id, name, address, age, city) VALUES (3, 'Michael Johnson', '789 Oak St', 40, 'Chicago');INSERT INTO test_table (id, name, address, age, city) VALUES (4, 'Emily Davis', '321 Pine St', 35, 'San Francisco');INSERT INTO test_table (id, name, address, age, city) VALUES (5, 'Sophia Lee', '579 Spruce St', 29, 'Dallas');INSERT INTO test_table (id, name, address, age, city) VALUES (6, 'David Wilson', '654 Maple St', 28, 'Boston');INSERT INTO test_table (id, name, address, age, city) VALUES (7, 'Sarah Thompson', '987 Cedar St', 32, 'Seattle');INSERT INTO test_table (id, name, address, age, city) VALUES (8, 'Daniel Anderson', '135 Walnut St', 38, 'Houston');INSERT INTO test_table (id, name, address, age, city) VALUES (9, 'Olivia Martinez', '864 Cherry St', 27, 'Miami');INSERT INTO test_table (id, name, address, age, city) VALUES (10, 'James Taylor', '246 Birch St', 33, 'Denver');

export ORACLE_HOME=/u01/app/halo/product/instantclient_12_2export OCI_LIB_DIR=$ORACLE_HOMEexport OCI_INC_DIR=$ORACLE_HOME/sdk/includeexport LD_LIBRARY_PATH=$ORACLE_HOME/sdk/include/lib:$LD_LIBRARY_PATH

SELECT * FROM orclfdw_foreign_table@orclfdw_oracle_server;