I have an OLTP database and ETL jobs are running in the same database in the background. I was thinking of separating the OLTP and ETL instances so that resource utilization would be distributed. Basically, the OLTP instance should have fewer or no ETL overhead. The idea is to create foreign tables on the ETL instance connecting to OLTP remote server using *postgres_fdw*. I understand that Postgres will fetch chunks of data from the remote server using the cursor. Can someone please help me if my understanding is right that running a complex query including foreign tables would use resources(RAM,CPU) from the local server? and is the remote server safe from these executions overhead? And if I am wrong which instance resources would Postgres use to run a complex SQL with joins on foreign tables? Thanks in advance!

I have a query on an Oracle database that involves accessing a remote database through a database link. Here's the query:

SELECT t1.col1 , t2.col2 
FROM Table1 t1 
LEFT JOIN Table2@dblink t2 ON (t1.id=t2.id)

When executing this query, I understand that it will open one session in the local database. However, I'm uncertain about the number of sessions that will be opened in the remote database query and will be executed from java application. For instance, if I have approximately 100,000 records to retrieve, how many sessions will be opened in the remote database during the execution of this query? I appreciate any insights or explanations regarding the session management behavior in this scenario.

I'm trying to find the best way to load a remote table with a blob column (blob only contains text, so clob would have been better). So far we've been able to reduce loadtime from 218 minutes to 2 minutes using a view on the source table and converting the blob to varchar2 using (simplified):

CREATE OR REPLACE FORCE EDITIONABLE VIEW "TABLE_V" ("ID", "DATE", "CLOB_PT1", "CLOB_PT2", "CLOB", CONSTRAINT "TABLE_V_PK" PRIMARY KEY ("ID") RELY DISABLE) AS
  SELECT
  "ID",
  "DATE",
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB, 2000, 1)) "CLOB_PT1",
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB, 2000, 2001)) "CLOB_PT2",
  CASE WHEN LENGTH(INHOUD) > 4000 THEN TO_CLOB(BLOB) END "CLOB"
  FROM OWNER.TABLE;

Because we cannot guarantee the size of the blob will continue to fit in our two varchars we put anything bigger into a separate clob column in the view. now the idea is to merge the varchars or clob back to one clob in our staging area. Here come's the challenge of ORA-22992: cannot use LOB locators selected from remote tables Concatenating the two varchars into the targeted clob column works fine.

INSERT 
  /*+  APPEND PARALLEL  */ 
  INTO SA.TESTTABLE
  (
    ID ,
    DATE ,
    CLOB
  ) 
SELECT 
  TABLE_V_1.ID ,
  TABLE_V_1.DATE , 
  TABLE_V_1.CLOB_PT1||TABLE_V_1.CLOB_PT2
FROM 
  OWNER.TABLE_V_1@DB_LNK TABLE_V_1

Inserting the clob into the targeted clob column works fine.

INSERT 
  /*+  APPEND PARALLEL  */ 
  INTO SA.TESTTABLE
  (
    ID ,
    DATE ,
    CLOB
  ) 
SELECT 
  TABLE_V_1.ID ,
  TABLE_V_1.DATE , 
  CLOB
FROM 
  OWNER.TABLE_V_1@DB_LNK TABLE_V_1

Using a 'case when' inserting either the concatenated varchars or the clob column fails!

INSERT 
  /*+  APPEND PARALLEL  */ 
  INTO SA.TESTTABLE
  (
    ID ,
    DATE ,
    CLOB
  ) 
SELECT 
  TABLE_V_1.ID ,
  TABLE_V_1.DATE , 
  CASE WHEN CLOB IS NOT NULL
    THEN TABLE_V_1.CLOB_PT1||TABLE_V_1.CLOB_PT2
    ELSE TABLE_V_1.INHOUD_CLOB
  END
FROM 
  OWNER.TABLE_V_1@DB_LNK TABLE_V_1

I also tried concatenating all three columns by default, (varchars1 and 2 will be empty if size blob >4000), same error. Why is this failing? Any suggestions on getting this to work?

Is it possible for me to have Oracle MVIEW log in my source database and refresh a Postgresql database MVIEW using this MVIEW log? I know I can query the MVIEW log, I have done that. But since it is not a good practice to query a log, is it even possible to use an MVIEW log from Postgresql just like an Oracle MVIEW uses a log? If that's not possible, is a dblink from Postgresql to Oracle possible?

I'm having two DB server (PostgreSQL, Oracle). I'm able to access the table in PostgreSQL from Oracle through DB-Link. I've use the below query to access the table. select "Cust_Id", "Mobile_No" from "schema_name"."tablename"@Dblink where "cust_id" = '123456789'; As same as I'm trying to access the function in PostgreSQL from Oracle server through DBLink. But I can't access it. I've used below query select "Cust_id" from "schema_name"."function_name"@DbLink('123456789'); The function will return the mobileno and cust_id in table type. I'm getting error like. > SQL Command not properly ended Pls help on this issue. Thnx in advance.

I have **Oracle 12c** running on **Solaris** which is my main database for multiple systems and data processing. There is planned upgrade and migration from Solaris but this wont happen soon, my team is not responsible for this. I need to create **Database link** to **MariaDB**. This MariaDB Server is owned by different team and run some third party system for me. The problem is **MariaDB** is configured with **dialog authentication method** which is **not supported by MySQL**. This means I cannot use MySQL driver, only MariaDB driver. Unfortunately i cannot find any drivers for Solaris. The only solution I can image is to download source of MariaDB driver and build for Solaris but this is tricky for me. **Is there any other solution i can use?**

I have simple question but i could find any information about it. I also run some tests but didn't get any results that i measurable. Let say i have same structure with same data on my db and second db. I have same query (second query is with db link). Will it be any performance downgrade when i will query different database by db link on same physical server? regards

I have the environment that I explain below and I need to encrypt the data that is sent between my two nodes, I have been reading but I can not find the right solution, I explain: I have an Oracle Database 12c in which I have Advanced Security implemented through the netmgr but the encryption between the databases does not work, when putting a capture of the packages with tcpdump is shown in plain text when I use dblink, these dblinks are they use to load information from different sources (Oracle, PostgreSQL and SQL Server ... the latter using Oracle Gateway). How can I encrypt the communication between the source and the destination and vice versa to use the dblinks? Thank you very much.

I have two Docker containers with a Postgres db in each. I am exposing the Postgres socket of each container to the other one, using Docker volumes, like so:

docker run --rm -itd --name=containerone -p 127.0.0.1:3003:3000 -v containeronepgsocket:/var/run/postgresql -v containertwopgsocket:/var/run/postgresql_containertwo registry.gitlab.com/mycont/mycont

The above makes it so that in the container I have the regular socket /var/run/postgresql, as well as the socket /var/run/postgresql_containertwo My objective is to be able to query each container from within the other one, by using the Postgres peer authentication method (i.e. no need for a password). I have managed to accomplish this when querying through javascript. Here is my code, using the Sequelize package from within container one to query container two:

const sequelize_containertwo = new Sequelize('containertwo', 'myuser', undefined, {
    host: '/var/run/postgresql_containertwo',
    dialect: 'postgres'
});

As you can see, the password is left as undefined. As long as the socket is in the OS, and I am using the right OS user, the peer auth method works and I am able to query the other container. But I also have PL/pgSQL functions and I need to query the other container from within those functions. And this is where I am hitting a problem. From within the functions, I am querying the same container but I also need to query data from the other container. To accomplish this, my first thought was to use dblink. I tried to make something like this:

SELECT * 
  FROM dblink('postgresql:///containertwo_db?host=/var/run/postgresql_containertwo',
    SELECT mycol
    FROM mytable
    )
    AS t1(mycol BIGINT);

This provides the host as a parameter in the connection string. I have already had success connecting to the socket using exactly this connection string but from the docker_entrypoint file. When I try from within dblink, I get the error that a password is required. How can I query the other container from within a PL/pgSQL function and using peer authentication? Can I do it through dblink, and if so, how? Or is there some other way?

Besides manually searching for all the dblink() used in triggers/functions/stored procedures is there a way to query for this information? Example: There is a dblink inside of a trigger, can I select something from the postgres schema that would identify this? I'm thinking this is a manual effort but asking just to make sure I'm not missing something. NOTE: I have some logging where I am also looking for dblink()

I am trying to establish a database link between Oracle, which is installed on a Linux server, and MSSQL, which is installed on a separate Windows server. I have already: - Successfully tested the DSN - Queried data from the MSSQL database from the Linux server using isql and sqlcmd tools - Created the initSID.ora file in $ORACLE_HOME/hs/admin - Configured the listener.ora file and tested if the instance is up - Configured the tnsnames.ora file and successfully tnsping it. - Successfully created the database link However, when I try to use the database link in Oracle to query data from MSSQL, I get the following error: ERROR at line 1: ORA-28500: connection from ORACLE to an non-Oracle system returned this message: ORA-02063: preceding line from DBLINK_NAME **I have checked the following:** - The Oracle Net Listener is running and configured to listen on the correct port. - The MSSQL server is running and accessible from the Linux server. - Allowed Remote Connections in MSSQL Server I am not sure what else to check. Can anyone help me troubleshoot this issue? **Additional information:** - MSSQL version: 2012 - Oracle version: 12c - Linux distribution: OEL 7.4 - ODBC driver: dg4odbc ,mssql-tools, libmsodbcsql17 - Error code: ORA-28500, ORA-02063 Edit: I am adding further information that might be useful for investigation: $ORACLE_HOME/hs/admin/initMSSQL.ora: HS_FDS_CONNECT_INFO = MSSQL HS_FDS_TRACE_LEVEL = user HS_FDS_SHAREABLE_NAME = /usr/lib64/libmsodbcsql-17.so HS_FDS_TRACE_FILE_NAME=/tmp/ora_hs_trace.log HS_NLS_NCHAR = UCS2 $ORACLE_HOME/network/admin/listener.ora: SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=DBTEST) (ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1) (PROGRAM=dg4odbc) ) (SID_DESC= (SID_NAME=MSSQL) (ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1) (PROGRAM=dg4odbc) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = geno.test)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle $ORACLE_HOME/network/admin/tnsnames.ora: MSSQL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SID = MSSQL) ) (HS = OK) ) I appreciate any help you can provide. Thank you!

I assumed that dblink function were not rolledback, but in the use case below it is. To summarize: Function1 is a dblink call creating a table. Function2 calls function1. Function2 is defined as RETURNS text missing the RETURNS statement. On execution, function2 executes function1 then returns ERROR : Proccess reached end without return. The table from dblink is not created. Anyone has an explanation? Function1: ~~~ CREATE OR REPLACE FUNCTION function1 (_user text,_pwd text) RETURNS text AS $BODY$ DECLARE conn_string text; BEGIN conn_string := FORMAT('port = 5432 host=%1$s dbname=%2$s user=%3$s password=%4$s','host','db',_user,_mdp); DROP TABLE IF EXISTS foo; EXECUTE FORMAT('CREATE TABLE foo AS (SELECT * FROM dblink(''%1$s'',''SELECT * FROM bar'') AS t(id int, _text character varying, geom geometry(point)));',conn_string); RETURN 'foo created'; END; $BODY$; ~~~ Function2 : ~~~ CREATE OR REPLACE FUNCTION function2(_user text,_pwd text) RETURNS text DECLARE BEGIN PERFORM function1(_user,_pwd); END; -- END OF FUNCTION REACHED WITHOUT RETURN $BODY$; ~~~ I understand that function2 is not a working function. What I don't is the fact that function1 (and the dblink within) is rollbacked and would like to understand why.

I have two PostgreSQL servers: server 1: 192.168.0.1 server 2: 192.168.0.2 I want to connect to the database on server 2 from server 1. I do it like this: SELECT dblink_connect('hostaddr=192.168.0.2 port=5432 dbname=mydb user=postgres password=mypasswd'); My question is that when I do such connect my password is send as plaintext or its somehow encrypted during the transmission to the server 2? I use PostgreSQL 9.3 on Linuxx Mint 16 (server 1) and Xubuntu 13.10 (server 2).

I want to create a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c) via a dblink. - The MV would refresh on a 30-second schedule (since on-commit is not possible over a dblink). - The table in the external system does not have a primary key. ----------- I'm a novice. Here's what I've tried: --in the external system: create materialized view log on external_system.workorder with rowid; --in the primary system: create materialized view primary_system.workorder_mv build immediate refresh fast start with sysdate next sysdate + (30/(60*60*24)) as select cast(workorderid as number(38,0)) as objectid, wonum, status, --other fields longitudex, latitudey from external_system.workorder@gistomax -------------------- When I try the above, I get a litany of errors, starting with one that says I need a primary key. I found a page that suggests that it's possible to do a fast refresh over a dblink . But the example uses a primary key, not a rowid. And the docs say : > Rowid materialized views are not eligible for fast refresh after a > master table reorganization until a complete refresh has been > performed. But I don't really know what that means. And to be honest, I'm so new at this, that I know I'm barking up the wrong tree, so I'll stop there. -------------------- **Question:** Is it possible to create a fast-refresh MV over a dblink on table that doesn't have a PK?

Source database: Oracle 19c Target database: Oracle 12c, via dblink Is it safe to sync tables from a higher version to a lower version? 1. I will create a materialized view on the target with scheduled refresh. 2. Then I will insert it to target tables. Is this safe? The reason for my question is that, when using datapump, based on my experience, when the source database is of a higher version than the target, I need to specify [version=12.0.2.6] in the .par files when exporting from Oracle 19c to 12c.

I want to to replicate A TABLE from one server A to another B so that A and B always in-sync. This table on server A has like 4 - 5 million rows, whereas the table B is empty. What I want to do is doing this by pushing data from A to B. I will be using TRIGGER on Server A via either FDW or DBLINK. If there is CRUD operation on A, then it will immediately pushed into B. Previously, FWD has been used on another table, but it really really slow. I want to take a precaution of doing this before I proceed. Questions: 1. Why FWD is slow? This is the newest method of cross-database operation right? 2. For the start, Initially I need to copy data Server A into server B. Then turning on the trigger. Correct? 3. Is FWD better than DBLINK? If so, what makes it better and what does not? 4. Is there any tool that I can use to achieve this? There is Pentaho. But what else are the options? Many thanks

I have an Oracle 12.1.0.2 database that dblinks to an Oracle 10.2.0.3.0 database. I am upgrading the Oracle 12.1.0.2 database to 19c (12.2.0.3). Will my dblinks to the Oracle 10.2.0.3.0 database continue to work?

I created a db link from Oracle (19c) to MySQL (8.0). My MySQL table's columns datatypes are text (I mustn't change the datatype). There are 126 columns in the table (It must be such). When I try to query to MySQL in oracle sqlplus it gives "ORA-00997: illegal use of LONG datatype" error. I tried to many thing including creating a view in MySQL with casted fields to char or varchar but not solve the problem. When I do "desc view_table@dblink" in oracle sqlplus columns are still appeared as long datatype. Is there a way to solve it? What can I do? I stucked here.

We just upgraded from 11g SE to 12c SE. Please note, this process worked perfectly under 11g, and only started failing once under 12c. The procedure puts together a query, and then opens up a REF Cursor to return the data. The query has remote tables which are accessed via DB Link. If I take that query by itself, and paste it into SQL Plus, it works fine. But, if I run the procedure, and try to print the REF Cursor, it just hangs, forever. What changed between 11g & 12c to make this start failing?

I have SQL Server 2014 and I'm using SQL Server Agent to start some scheduled procedures during the day. These procedures make some selects (in DBlink) on an Oracle database that is handled by another company. Sometimes the Oracle database is offline and my procedures seem to be stucked and I should restart the entire server the make the SQL Server Agent work again. What am I doing wrong? I suppose there is a sort of timeout to end the procedure and release the SQL Agent. Example of select: SELECT * from openquery(positions, 'SELECT * from oracleTableDbLink.POSITION' )