Questions tagged [dblink]

For questions about the Oracle feature that allows accessing data in a different database, or possibly on a different server.

Filter by
Sorted by
Tagged with
0 votes
1 answer
17 views

Why is dblink nested function rolling back?

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 ...
Boodoo's user avatar
  • 45
0 votes
0 answers
131 views

Copy a partition from one database to another in postgres

I have a range (day wise) partitioned table managed using pg_partman extension and replica of the same is created in a different database. I want to copy a partition specific of that day into another ...
Akil Vohra's user avatar
0 votes
1 answer
38 views

postgres_fdw: from where, is the resource utilized? remote or local?

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. ...
Sajith P Shetty's user avatar
1 vote
0 answers
208 views

Is it safe to sync Oracle 19c to 12c via dblink?

Source database: Oracle 19c Target database: Oracle 12c, via dblink Is it safe to sync tables from a higher version to a lower version? I will create a materialized view on the target with scheduled ...
Alex Borja's user avatar
0 votes
0 answers
54 views

Can I run scheduled job as sysdba?

I have user c##admin in my core DB (Oracle 19c), with "sysdba" privileges. I want it to select from a pdb using a database link without giving a password. assuming the following: PDB Name: ...
SHR's user avatar
  • 862
0 votes
1 answer
179 views

POSTGRESQL table replication between 2 servers

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 ...
padjee's user avatar
  • 147
0 votes
1 answer
376 views

How to convert UTF8 data from PostgreSQL to AL32UTF8 Oracle DB?

I have a task to import some data from Postgres database to Oracle via dblink The connection between Postgres and Oracle works good, but unfortunately, when I try read data from created view (in ...
WBGAD's user avatar
  • 1
0 votes
0 answers
329 views

ORA-00997: illegal use of LONG datatype error when query from oracle to mysql over dblink

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 ...
Harun Cetin's user avatar
0 votes
0 answers
145 views

Oracle 12c - Opening cursor with remote tables over db link

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 ...
Landon Statis's user avatar
0 votes
1 answer
657 views

Same user cannot 'see' view on remote oracle database athough dblink is valid

I am running an impdp job that I have extracted the DDL from to determine why the job hangs. It consistently stops on the first object created using a dblink. I am running the impdp job as system as ...
Daniel Grindstaff's user avatar
0 votes
1 answer
78 views

MV on remote/master table fails to resume syncing (after master db host was temporarily unreachable)

I have a fast-refresh materialized view in a target db (18c) that syncs from a remote table/master db (19c): --master db (19c) create materialized view log on master.workorder with primary key; --...
User1974's user avatar
  • 1,427
0 votes
2 answers
1k views

Create fast-refresh MV over dblink on table without PK?

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 ...
User1974's user avatar
  • 1,427
0 votes
1 answer
755 views

Materialized view not purging (over dblink via view)

I've created a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c): create materialized view primary_system.workorder_mv build immediate refresh force on ...
User1974's user avatar
  • 1,427
0 votes
1 answer
2k views

inserting blob/clob over dblinks

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 ...
R. Sluiter's user avatar
1 vote
3 answers
5k views

Can an Oracle 19c database dblink to an Oracle 10.2.3 database?

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 ...
Darcy Dommer's user avatar
0 votes
2 answers
474 views

Postgresql MVIEW refresh from Oracle Materialized View Log

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 ...
MacJava's user avatar
-1 votes
1 answer
16 views

Query Oracle Database On Who Has Create Database Link Privs (private)?

Anybody know a good way to query the entire Oracle database on what users have privs to make private database links? Thank you!!
Austin's user avatar
  • 3
0 votes
0 answers
258 views

Transport-level error when I try to launch a select from SQL Server to Oracle via Dblink

I'm trying to launch a select from Sql Management Studio (SQL Server 2014) on a DBlink that points on an Oracle database that is handled by another company. The results begin to come back to the ...
Koba's user avatar
  • 13
1 vote
2 answers
296 views

SQL Server Agent crashes when scheduled procedures make select in DBLink on an offline Oracle database

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 ...
Koba's user avatar
  • 13
0 votes
1 answer
1k views

How to get TNS connect strings (like tnsping) to check a dblink via a session?

I would like to know, if there is any chance to get the currently used TNS connect string, that will be used for a DB-Link from a db-server. I'm connected to a schema on a database located on any ...
D. Lohrsträter's user avatar
1 vote
0 answers
270 views

ORA-02063 Encoding problem with Oracle querying SQL Server through ODBC link

We have a problem querying some data from the SQL Server from the Oracle database with the ORA-LNX connecting to SQL-SRV using ODBC and Heterogeneous Service (Oracle HS). When we send the query ...
MensSana's user avatar
  • 111
1 vote
1 answer
2k views

How to securely store the password for an Oracle DB link?

Having a system consisting of two linked Oracle DBs, we currently have the password for the dblink stored in a table, in order to be able to change it (session issues) and to re-created the dblink in ...
stmoebius's user avatar
  • 197
-2 votes
3 answers
808 views

Is Oracle DBLink Vulnerable? [closed]

We have a web page on internet that calls a web service located on our intranet. The web service talks to Database A to get some data. Database A has a DBLink to Database B. This link is used to ...
Nina's user avatar
  • 139
0 votes
2 answers
2k views

Azure Database Link to another Azure database

How to query between two databases with the same server in SQL Database? Currently we are aiming to link two azure databases that will update simultaneously. Thanks!
Nathan's user avatar
  • 29
1 vote
1 answer
739 views

How do you select "array"-type columns via the DBLINK function?

Using PostgreSQL, I can easily do a select statement to return a column that is an array of the datatype point. i.e. colA is the datatype point[] select colA from tbl Returns a column that contains ...
Zach Smith's user avatar
  • 2,278
1 vote
0 answers
182 views

Oracle procedure takes forever to compile if I use public synonym

I have a procedure that queries a table through a DB link. If my procedure uses a public synonym through the DB link, it takes forever to compile. I'm creating the procedure in Oracle 12c. The DB ...
user184794's user avatar
0 votes
2 answers
387 views

Issue with Identifiers in Postgres greater than Oracle's limit while connecting over ODBC (DBLINK)

We have a database link from Oracle 12.1 to Postgres via psqlODBC Driver. There is a problem while running few queries/statements when the Postgres identifiers, like the length of a column name ...
Kaushik Nayak's user avatar
4 votes
2 answers
8k views

PostgreSQL error: remote query result rowtype does not match the specified FROM clause rowtype, on remote function call

This is my remote function: CREATE OR REPLACE FUNCTION public._test1() RETURNS record LANGUAGE plpgsql AS $function$ DECLARE rec record; BEGIN select 1,2 into rec; return rec; END $function$; ...
Kenobi's user avatar
  • 143
0 votes
0 answers
2k views

Oracle dblink to MS SQL Server

Can I create a dblink to a SQL Server from Oracle, in the same I would if I was creating a dblink from Oracle to Oracle? I've tried, but get a message saying - connection has closed as partner has ...
KS1's user avatar
  • 181
2 votes
1 answer
767 views

How to parallelise remote queries in Postgres

I am querying N tables remotely using dblink. This queries represent a sort of "sharding" of the data. Each remote query takes roughly the same time (~ 0.5 sec). However when I gather all the data in ...
Randomize's user avatar
  • 1,153
1 vote
1 answer
3k views

Postgresql: Select a table from different database and different host

I know this is not new, but I have not found a solution, even though I have followed several posts in this forum. I'm trying to show table from different database using dblink extension. Installing ...
Ugy Astro's user avatar
  • 111
1 vote
1 answer
4k views

How to Oracle DBLink encryption

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 ...
Arnaldo Raxach's user avatar
0 votes
2 answers
674 views

Can I use postgres_fdw without foreign tables defined?

I have a production database "PRODdb1", with a read-only user account. I have a need to query(select statement) this database and insert the data into a secondary database named "RPTdb1". I originally ...
calKno's user avatar
  • 1
0 votes
1 answer
1k views

Unable to query remote schema object using partial database link name

I came across a very interesting situation today and I'm putting this question to the community in hopes that someone can explain why this happened. I haven't been able to find an explanation in ...
hunterjrj's user avatar
  • 131
1 vote
1 answer
1k views

How to access PostgreSQL Functions from Oracle through DBLink?

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" ...
Ganapathy's user avatar
  • 483
2 votes
5 answers
11k views

IN clause too slow

Ive been searching for about 1 hour and i have not found anything. I have this problem: When I do this query: select order_id from or_order@smartflex where order_id in ( select distinct numeroot ...
user3126458's user avatar
1 vote
0 answers
416 views

Connect ORACLE to SQLSERVER to REMOTE SQL SERVER

I have Sql server and oracle installed on same machine. So I created a heterogeneous connection between the two server. Now I need to do some transaction in my sql server and need to send the result ...
User12345's user avatar
  • 209
0 votes
1 answer
2k views

Unable to access tables in sql server using oracle dblink

I have created a Heterogeneous connection between oracle and sql server. and tested with below query, SELECT * FROM sys.tables@SQLSERVER; Then I have created a new Database in SQL server called "...
User12345's user avatar
  • 209
1 vote
1 answer
419 views

Auditing authentication issues in a distributed environment

I'm trying to get a better handle on what locked out a specific user when multiple Oracle databases are involved using database links. Setup: sqlplus kjohnston@db2 create user testuser identified ...
Kris Johnston's user avatar
2 votes
1 answer
3k views

Creating Synonym For a Table in PostgreSQL over dblink?

I have two PostgreSQL databases connected over dblink extension. I want to create synonym for a table in first database so the queries that are coming through application I can route them to the table ...
Sayad Xiarkakh's user avatar
1 vote
1 answer
566 views

Updating records in MS Access linked table will activate triggers?

I have a simple yet undocumented (or I could not find the documentation to solve my uneasiness) question, I hope I have come to the right place: I am using MS Access to act as a poor front end for ...
Feillen's user avatar
  • 191
1 vote
0 answers
460 views

Oracle DB link: Error line missing

We have a DB link between 2 Oracle 11.2 databases. On the production system, we noticed that when an UPDATE statement of a remote table fails in an PL/SQL procedure (started as a scheduled job), the ...
David Balažic's user avatar
2 votes
1 answer
2k views

Call dblink from a trigger function

I have an environment where I have to move data from one database to another once a day. I Try to solve this by calling 'dblink' from a triggered function. The following statement I can execute from ...
omanthey's user avatar
  • 123
1 vote
1 answer
67 views

Accessing an external database through pgAdmin installed on my local machine?

I want to query a database stored in an external location. Up to now, I was using Microsoft Access for querying, but due to limitation of 2 GB output file, I am trying to switch to another DBMS, ...
Beginner's user avatar
4 votes
2 answers
8k views

Oracle 12c - Create materialized view results in ORA-00942

I have a view that I can access through a database link as such: SQL> select count(*) from REMOTE_SCHEMA.REMOTE_VIEW@REMOTE_DB; COUNT(*) -------- 110 I can create and successfully query the ...
user109164's user avatar
1 vote
1 answer
3k views

alter public database link to private in oracle

I have a database link in my database which is created as public as below. CREATE PUBLIC DATABASE LINK dblink_name CONNECT TO usernane USING 'tag'; I have a requirement to convert it to a PRIVATE ...
hemalp108's user avatar
  • 113
3 votes
1 answer
4k views

Is there an equivalent to OPENROWSET in Oracle?

Is there an equivalent to OPENROWSET in Oracle? From OPENROWSET (Transact-SQL): This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and ...
User1974's user avatar
  • 1,427
1 vote
1 answer
7k views

What does remote database have to do for DB link in Oracle

In local database, it has to create DB LINK through CREATE DATABASE LINK. What about remote database side, what does it have to do? On fixed user link, local database need to know ID and password set....
SangminKim's user avatar
3 votes
3 answers
10k views

Connecting Oracle 11g to Postgres via dblink

I have administrator access to our Oracle database , but was wondering if its possible to connect an Oracle 11g database to Postgres database and if so, what steps would I need to follow. I've seen ...
Tikkaty's user avatar
  • 131
1 vote
1 answer
744 views

Why can't I restore the DB from an SQL dump in PostgreSQL 9.4 (problem with dblink)?

I have created a dump of a DB using: pg_dump db_name > db_dump.sql and proceed to restore it with: psql --single-transaction db_name < db_dump.sql Prior to attempting to restore the DB, I ...
dw8547's user avatar
  • 867