Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [dblink]

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

76 questions
6
votes
3 answers

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 something from the postgres schema that would…
Phill Pafford
  • 1,415
  • 6
  • 19
  • 26
6
votes
1 answer

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 the following code, which uses a trust account…
vol7ron
  • 417
  • 1
  • 6
  • 13
5
votes
1 answer

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 LINK my_link CONNECT TO daniel identified by…
daniel9x
  • 391
  • 2
  • 12
  • 20
4
votes
2 answers

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$; This is my local function call: SELECT x.a,…
Kenobi
  • 143
  • 1
  • 2
  • 5
4
votes
2 answers

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 remote view through a synonym: SQL> create synonym…
user109164
  • 61
  • 1
  • 4
4
votes
1 answer

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 accessing remote data by using OLE DB.
User1974
  • 1,517
  • 25
  • 54
3
votes
3 answers

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 single table causes me lots of headache. It is a…
Alexey
  • 139
  • 1
  • 2
3
votes
0 answers

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 from table1@db2; ... end / for some reasons the…
user37143
  • 45
  • 1
  • 1
  • 5
3
votes
3 answers

Altering same table across multiple databases

I have multiple databases on the same instance and I am in the process of updating a table schema that must propagate across all the databases. I am not sure I know what the right procedure for this is but I think it should go a little something…
GuidoS
  • 1,047
  • 1
  • 8
  • 7
3
votes
3 answers

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 a few tutorials mention an ODBC driver that needs…
Tikkaty
  • 131
  • 1
  • 1
  • 3
2
votes
2 answers

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 DB_DOMAIN. If the local database's DB_DOMAIN was…
exit_1
  • 207
  • 1
  • 4
  • 14
2
votes
1 answer

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 from that table over the dblink. I appear to be…
MK01
  • 451
  • 3
  • 11
  • 26
2
votes
1 answer

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?
lit
  • 223
  • 3
  • 15
2
votes
2 answers

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 error ORA-02049: timeout: distributed transaction…
Avnish Garg
  • 31
  • 1
  • 1
  • 4
2
votes
1 answer

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 a similar existing merge that works fine (but…
michel-lind
  • 211
  • 3
  • 9
1
2 3 4 5 6