Questions tagged [dblink]
For questions about the Oracle feature that allows accessing data in a different database, or possibly on a different server.
53
questions
1
vote
0answers
23 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 ...
0
votes
2answers
49 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 ...
1
vote
1answer
461 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$;
...
0
votes
0answers
103 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 ...
1
vote
1answer
73 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 ...
1
vote
1answer
201 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 ...
0
votes
0answers
24 views
Utilizing DBLINK[_EXEC] to control transactions within PL/PgSQL functions?
I am exploring options to control transactions within functions, mainly to reduce the memory footprint for large operations over multiple individual units. I.e. large data-sets are inserted regularly, ...
0
votes
1answer
626 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 ...
0
votes
2answers
182 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 ...
0
votes
1answer
595 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 ...
0
votes
0answers
215 views
How to set up from scratch distributed transaction between SQL Server and Oracle?
For one of our client, I have to solve a problem regarding intermittent transactions failures between an SQL Server 2012 DB engine and an Oracle 12c DB engine.
(OLE DB provider "OraOLEDB.Oracle" for ...
1
vote
1answer
437 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" ...
1
vote
5answers
2k 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 ...
1
vote
0answers
308 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 ...
0
votes
1answer
679 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 "...
1
vote
1answer
253 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 ...
2
votes
1answer
2k 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 ...
1
vote
1answer
366 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 ...
1
vote
0answers
367 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 ...
2
votes
1answer
753 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 ...
1
vote
1answer
42 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, ...
3
votes
2answers
4k 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 ...
1
vote
1answer
2k 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 ...
2
votes
1answer
2k 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 ...
1
vote
1answer
3k 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....
3
votes
3answers
7k 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 ...
1
vote
1answer
469 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 ...
0
votes
1answer
2k views
Want to remove global_name from DB links (Oracle 11g)
I am trying to call remote function via DB LINKS.
However, My global name contains the string ON and I believe it causes an error.
The Error I have is ...
SQL> var tmp varchar2(2);
SQL> ...
3
votes
1answer
11k views
How to create an Oracle Database Link when the password has special characters
I'm trying to create a DBLink. SQL Developer is the client I'm using. The password to my remote user has *'s in it. I'm having trouble figuring this out. This syntax isn't working:
CREATE DATABASE ...
1
vote
2answers
10k views
Why am I able to query a remote database without a dblink?
While researching a security concern, I discovered that users are able to query a remote database without the need of a dblink.
However, this was only true for databases that had no value for ...
3
votes
3answers
5k views
query to find all dblink()
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 ...
0
votes
2answers
556 views
How to prevent users other than DBAs from creating database links
To begin, please keep in mind that I am a very green DBA. I have done prior research and have not really found anything too promising, or maybe it just seems that way because I am still struggling in ...
-1
votes
5answers
5k views
What is a DB Link good for? [closed]
Here is defined what a DBLink is but I really understand for what kind of situations you need it?
What I could imagine is a situation where I have a limited (in size like sql-server express) ...
-2
votes
1answer
713 views
Postgresql function to create table in other database
I Want a function to create Table in the different database , Database name is the argument for the function and table structure and names are static {No need to pass as the arguments}
2
votes
1answer
3k views
transactions and Oracle dblink
I have 2 dbs, let's call them writedb and readdb. Readdb has a dblink to writedb and a view to a table over that dblink.
I do an update on writedb to that table which view points to and then read ...
0
votes
1answer
291 views
ORA02063 error while connecting to remote databse
I have connected to remote database using DB link. From prod databases it works fine but from test env reporting error invalid username/password. Please help.
0
votes
1answer
561 views
Results of SAMPLE clause using a DBlink between 2 Oracle databases not randomised
I'm using the SAMPLE clause to retrieve random rows from a table. It works fine except when the table is from another database. I'm using a database link between 2 Oracle 11g databases (11.2).
This ...
2
votes
1answer
30 views
ANSI SQL for links
Oracle provides a "link" mechanism in order to access a different database instance than the one on which the query is running.
DIM_DATES@OTHERDB
Is there any ANSI SQL standard way to code this?
1
vote
0answers
259 views
Multischema view for postgres , querying multiple databases
Im managing SaaS app where each customer has a separate database (Postgres 9.3) . I need to figure out the best way to aggregate and query this data ( READ-only ). I know postgres has problems with ...
2
votes
2answers
12k views
ORA-02049 and ORA-02063 Errors
Select For UPDATE NOWAIT
is performed on a table by 20 processes in parallel
The error below is returned by the 4 processes out of 20 (16 processes are successful)
SQL_UPDATE_ERROR- : SQL update ...
3
votes
3answers
2k views
How to avoid hardcoding a database link name in a package body
I have a PL/SQL package that copies data from a remote DB2 database using a database link. To keep the package configuration-independent I wrapped the remote table in a view. But a single field in a ...
0
votes
2answers
4k views
Alternatives for Dblink in oracle?
we have an SSIS job that inserts data into a table in server A from data in table which is located in some other server B.
For this,we are making use of dblinks to connect to other database . But, ...
0
votes
1answer
4k views
Setting up DBLink between two Sybase Databases
I am having dificulty finding anything on the net on this subject.
I am tasked with creating a DBLink between two Sybase Databases. At the moment it's not yet clear if they will be on the same server ...
3
votes
0answers
555 views
plsql procedure gets invalidated
procedure involving dblinks gets invalidated regularly .we need to recompile every time.How to find what is causing the problem
in my case
procedure p1()--resides in db1
is
begin
....
select col1 ...
2
votes
3answers
18k views
Create Oracle DB LINK to a database with no DB_DOMAIN
I'm trying to create a database link between this two DBs.
My local DB is 11.2.0.3.
SQL> select * from global_name;
GLOBAL_NAME
-------------------------------------------------------------------...
0
votes
1answer
3k views
PostgreSQL dblink password encrypted or not?
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
votes
1answer
4k views
Problem using merge into a remote table over dblink
I'm attempting to perform a merge into a remote table, let's say on server A (over a dblink), using a local table on server B. Source and target databases are both on Oracle 10.2.0.3.
We already have ...
1
vote
2answers
1k views
How to call Oracle's stored procedure from DB2 stored procedure?
I am new to writing stored procedures and wondering if it's possible to call a Oralce' stored procedure from DB2 stored procedure.
I have a scenario where a trigger on a DB2 table will invoke a DB2 ...
6
votes
1answer
2k views
PostgreSQL: DBLink weird permission/connection error
The goal is to (1) create a connection, (2) return a view of another database, and (3) close the same connection all to be stored in a view (no stored procedure or embedded password). I came up with ...
1
vote
0answers
1k views
Calling a function in remote database inside a stored procedure
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I ...
