Styjun

Can derivatives be defined as anti-integrals?

Olympic game scoring

Can Feather bring back a spell with Jump-Start?

How to give my students a straightedge instead of a ruler

What officially disallows US presidents from driving?

Does a succubus' Charm end when it dies?

Is there any reason to concentrate on the Thunderous Smite spell after using its effects?

How to control the output voltage of a solid state relay

Why is the Digital 0 not 0V in computer systems?

How do certain apps show new notifications when internet access is restricted to them?

A Mainer Expression

Make 1998 using the least possible digits 8

How to publish superseding results without creating enemies

Why don't Wizards use wrist straps to protect against disarming charms?

Python web-scraper to download table of transistor counts from Wikipedia

Examples of proofs by making reduction to a finite set

Why don't airports use arresting gears to recover energy from landing passenger planes?

Can a character with good/neutral alignment attune to a sentient object with evil alignment?

Why is my fire extinguisher emptied after one use?

Consonance v. Dissonance

What is the mathematical notation for rounding a given number to the nearest integer?

Why the car dealer is insisting on loan instead of cash

How are aircraft depainted?

What are these things that surround museum exhibits called?

Question on DBLink for 2 databases oracle

How to create a DB link between two oracle instancesHow does database indexing work?How to select the nth row in a SQL database table?MyISAM versus InnoDBHow to list the tables in a SQLite database file that was opened with ATTACH?How do I restore a dump file from mysqldump?Get list of all tables in Oracle?Best way to store password in databaseWhat are the options for storing hierarchical data in a relational database?Import SQL dump into PostgreSQL databaseReset identity seed after deleting records in SQL Server

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have a server with 2 databases: dbtest and checkdb.
I need that user user_b of dbtest can made a select on a table of other database.
How i have to set the dblink?

database oracle dblink

share|improve this question

asked Mar 28 at 11:33

Valerio DonatiValerio Donati

122 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  stackoverflow.com/questions/13267218/…
  
  – saman tr
  Mar 28 at 11:39
  

  
  
  
  

add a comment
 | 

0

I have a server with 2 databases: dbtest and checkdb.
I need that user user_b of dbtest can made a select on a table of other database.
How i have to set the dblink?

database oracle dblink

share|improve this question

asked Mar 28 at 11:33

Valerio DonatiValerio Donati

122 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  stackoverflow.com/questions/13267218/…
  
  – saman tr
  Mar 28 at 11:39
  

  
  
  
  

add a comment
 | 

I have a server with 2 databases: dbtest and checkdb.
I need that user user_b of dbtest can made a select on a table of other database.
How i have to set the dblink?

database oracle dblink

share|improve this question

asked Mar 28 at 11:33

Valerio DonatiValerio Donati

122 bronze badges

share|improve this question

asked Mar 28 at 11:33

Valerio DonatiValerio Donati

122 bronze badges

share|improve this question

asked Mar 28 at 11:33

Valerio DonatiValerio Donati

122 bronze badges

asked Mar 28 at 11:33

Valerio DonatiValerio Donati

122 bronze badges

asked Mar 28 at 11:33

Valerio DonatiValerio Donati

122 bronze badges

2 Answers
2

active

oldest

votes

0

On the top of your script run

alter session set global_names=false;

then whenever you need to fetch data from checkdb you add the link as a suffix. don't know exactly how your dbs are defined but something like

user_b is on dbtest

select * from table_name@dblink 
#might be that dblink is not the exact name. 

hope it helps.

share|improve this answer

answered Mar 28 at 11:41

dapazdapaz

39855 silver badges1515 bronze badges

add a comment
 | 

0

Connected as user in one of those databases, create a database link to another database following the syntax (https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CREATE-DATABASE-LINK.html#GUID-D966642A-B19E-449D-9968-1121AF06D793). It hasn't changed for ages, I suppose.

For example:

SQL> create database link dbl_scott -- database link name
 2 connect to scott -- you're connecting to this user (scott) ...
 3 identified by tiger -- ... which is identified by this password (tiger)
 4 using 'db11g:1521/orcl'; -- using clause has a database server (db11g):port (1521)/service name (orcl)

Database link created.

SQL> -- Testing; it has to return a row
SQL> select * From dual@dbl_scott; 

D
-
X

SQL>

The USING clause could be shortened by putting target database alias (the one written in TNSNAMES.ORA file on your database server). If you don't have access to that file (as you aren't a DBA), then the above option works OK.

Note that - even if you use invalid settings - database link might be created, but it won't work. For example:

SQL> drop database link dbl_scott;

Database link dropped.

SQL> create database link dbl_scott
 2 connect to xyzalksfjlaskfj
 3 identified by abc
 4 using '9809803242';

Database link created.

SQL> select * from dual@dbl_scott;
select * from dual@dbl_scott
 *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL>

Therefore, be careful.

share|improve this answer

answered Mar 28 at 11:46

LittlefootLittlefoot

33.6k77 gold badges1717 silver badges3636 bronze badges

add a comment
 | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55396571%2fquestion-on-dblink-for-2-databases-oracle%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

On the top of your script run

alter session set global_names=false;

then whenever you need to fetch data from checkdb you add the link as a suffix. don't know exactly how your dbs are defined but something like

user_b is on dbtest

select * from table_name@dblink 
#might be that dblink is not the exact name. 

hope it helps.

share|improve this answer

answered Mar 28 at 11:41

dapazdapaz

39855 silver badges1515 bronze badges

add a comment
 | 

0

On the top of your script run

alter session set global_names=false;

then whenever you need to fetch data from checkdb you add the link as a suffix. don't know exactly how your dbs are defined but something like

user_b is on dbtest

select * from table_name@dblink 
#might be that dblink is not the exact name. 

hope it helps.

share|improve this answer

answered Mar 28 at 11:41

dapazdapaz

39855 silver badges1515 bronze badges

add a comment
 | 

On the top of your script run

alter session set global_names=false;

then whenever you need to fetch data from checkdb you add the link as a suffix. don't know exactly how your dbs are defined but something like

user_b is on dbtest

select * from table_name@dblink 
#might be that dblink is not the exact name. 

hope it helps.

share|improve this answer

answered Mar 28 at 11:41

dapazdapaz

39855 silver badges1515 bronze badges

alter session set global_names=false;

select * from table_name@dblink 
#might be that dblink is not the exact name. 

share|improve this answer

answered Mar 28 at 11:41

dapazdapaz

39855 silver badges1515 bronze badges

answered Mar 28 at 11:41

dapazdapaz

39855 silver badges1515 bronze badges

answered Mar 28 at 11:41

dapazdapaz

39855 silver badges1515 bronze badges

0

Connected as user in one of those databases, create a database link to another database following the syntax (https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CREATE-DATABASE-LINK.html#GUID-D966642A-B19E-449D-9968-1121AF06D793). It hasn't changed for ages, I suppose.

For example:

SQL> create database link dbl_scott -- database link name
 2 connect to scott -- you're connecting to this user (scott) ...
 3 identified by tiger -- ... which is identified by this password (tiger)
 4 using 'db11g:1521/orcl'; -- using clause has a database server (db11g):port (1521)/service name (orcl)

Database link created.

SQL> -- Testing; it has to return a row
SQL> select * From dual@dbl_scott; 

D
-
X

SQL>

The USING clause could be shortened by putting target database alias (the one written in TNSNAMES.ORA file on your database server). If you don't have access to that file (as you aren't a DBA), then the above option works OK.

Note that - even if you use invalid settings - database link might be created, but it won't work. For example:

SQL> drop database link dbl_scott;

Database link dropped.

SQL> create database link dbl_scott
 2 connect to xyzalksfjlaskfj
 3 identified by abc
 4 using '9809803242';

Database link created.

SQL> select * from dual@dbl_scott;
select * from dual@dbl_scott
 *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL>

Therefore, be careful.

share|improve this answer

answered Mar 28 at 11:46

LittlefootLittlefoot

33.6k77 gold badges1717 silver badges3636 bronze badges

add a comment
 | 

0

Connected as user in one of those databases, create a database link to another database following the syntax (https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CREATE-DATABASE-LINK.html#GUID-D966642A-B19E-449D-9968-1121AF06D793). It hasn't changed for ages, I suppose.

For example:

SQL> create database link dbl_scott -- database link name
 2 connect to scott -- you're connecting to this user (scott) ...
 3 identified by tiger -- ... which is identified by this password (tiger)
 4 using 'db11g:1521/orcl'; -- using clause has a database server (db11g):port (1521)/service name (orcl)

Database link created.

SQL> -- Testing; it has to return a row
SQL> select * From dual@dbl_scott; 

D
-
X

SQL>

The USING clause could be shortened by putting target database alias (the one written in TNSNAMES.ORA file on your database server). If you don't have access to that file (as you aren't a DBA), then the above option works OK.

Note that - even if you use invalid settings - database link might be created, but it won't work. For example:

SQL> drop database link dbl_scott;

Database link dropped.

SQL> create database link dbl_scott
 2 connect to xyzalksfjlaskfj
 3 identified by abc
 4 using '9809803242';

Database link created.

SQL> select * from dual@dbl_scott;
select * from dual@dbl_scott
 *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL>

Therefore, be careful.

share|improve this answer

answered Mar 28 at 11:46

LittlefootLittlefoot

33.6k77 gold badges1717 silver badges3636 bronze badges

add a comment
 | 

Connected as user in one of those databases, create a database link to another database following the syntax (https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CREATE-DATABASE-LINK.html#GUID-D966642A-B19E-449D-9968-1121AF06D793). It hasn't changed for ages, I suppose.

For example:

SQL> create database link dbl_scott -- database link name
 2 connect to scott -- you're connecting to this user (scott) ...
 3 identified by tiger -- ... which is identified by this password (tiger)
 4 using 'db11g:1521/orcl'; -- using clause has a database server (db11g):port (1521)/service name (orcl)

Database link created.

SQL> -- Testing; it has to return a row
SQL> select * From dual@dbl_scott; 

D
-
X

SQL>

The USING clause could be shortened by putting target database alias (the one written in TNSNAMES.ORA file on your database server). If you don't have access to that file (as you aren't a DBA), then the above option works OK.

Note that - even if you use invalid settings - database link might be created, but it won't work. For example:

SQL> drop database link dbl_scott;

Database link dropped.

SQL> create database link dbl_scott
 2 connect to xyzalksfjlaskfj
 3 identified by abc
 4 using '9809803242';

Database link created.

SQL> select * from dual@dbl_scott;
select * from dual@dbl_scott
 *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL>

Therefore, be careful.

share|improve this answer

answered Mar 28 at 11:46

LittlefootLittlefoot

33.6k77 gold badges1717 silver badges3636 bronze badges

SQL> create database link dbl_scott -- database link name
 2 connect to scott -- you're connecting to this user (scott) ...
 3 identified by tiger -- ... which is identified by this password (tiger)
 4 using 'db11g:1521/orcl'; -- using clause has a database server (db11g):port (1521)/service name (orcl)

Database link created.

SQL> -- Testing; it has to return a row
SQL> select * From dual@dbl_scott; 

D
-
X

SQL>

SQL> drop database link dbl_scott;

Database link dropped.

SQL> create database link dbl_scott
 2 connect to xyzalksfjlaskfj
 3 identified by abc
 4 using '9809803242';

Database link created.

SQL> select * from dual@dbl_scott;
select * from dual@dbl_scott
 *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL>

share|improve this answer

answered Mar 28 at 11:46

LittlefootLittlefoot

33.6k77 gold badges1717 silver badges3636 bronze badges

answered Mar 28 at 11:46

LittlefootLittlefoot

33.6k77 gold badges1717 silver badges3636 bronze badges

answered Mar 28 at 11:46

LittlefootLittlefoot

33.6k77 gold badges1717 silver badges3636 bronze badges