1

i am trying to figure out, how to JOIN tables from two different oracle databases in SQLDeveloper, which I currently can access only isolated/separately from each other. The Problem is: Our company has restricted the CREATE DATABASE LINK privileges, so there is no chance to get this done this way. (The database is a read-only DB). Is there another way to join the databases? It is only necessary for diagnostics and data-flow observation.

Any help or hints appreciated.

Kind Regards.

Improve this question
4
  • You can use ColdFusion and Query of Queries. Commented Feb 8, 2018 at 12:35
  • i can kindly suggest one of my answers : stackoverflow.com/questions/48564496/… Commented Feb 8, 2018 at 12:37
  • 1
    not exactly a db link but the BRIDGE command in sqldev/sqlcl reaches to remote db and makes a local table from the remote. I can give you and example if that sounds like it could work. Commented Feb 8, 2018 at 14:54
  • @KrisRice Sure, thank you! An example would be very helpful! An alternative would be to create a local database and link the database from there, but the bridge-approach sounds very promising, too ;-) Commented Feb 8, 2018 at 15:16

2 Answers 2

Reset to default
3

Here's the BRIDGE command in sqldev/sqlcl that could help. This command takes in the 3 parameters

  • The name of the new local table to create
  • The full connect details to a remote database.
  • The sql to issue on the remote side.

When executed the tool will connect via jdbc to the remote database. Issue the sql. Retrieve the structure of the query to get the metadata to create the local new table specified. Then batch insert into the local table as it iterates the resultset of the remote query.

Caution! This command creates the table only. Any indexes will need to be added manually.

Here's a full example:

sql jeff/jeff    
SQLcl: Release 18.1 Production on Thu Feb 08 10:40:46 2018

JEFF@xe>tables
**TABLES**   
DEPT     


JEFF@xe>BRIDGE emp as "jdbc:oracle:thin:klrice/klrice@localhost:1521:xe"(select * from emp);

Created table emp and inserted 14 rows

JEFF@xe>tables
**TABLES**   
DEPT     
EMP      


JEFF@xe>select ename,dname
  2  from emp,dept
  3  where emp.deptno = dept.deptno;
ENAME    DNAME        
KING     ACCOUNTING   
BLAKE    SALES        
CLARK    ACCOUNTING   
JONES    RESEARCH     
SCOTT    RESEARCH     
FORD     RESEARCH     
SMITH    RESEARCH     
ALLEN    SALES        
WARD     SALES        
MARTIN   SALES        
TURNER   SALES        
ADAMS    RESEARCH     
JAMES    SALES        
MILLER   ACCOUNTING   


14 rows selected.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

As far as I can tell, you do need a database link in order to do what you've asked. If you can't create it, I'm afraid that you can't do that as there's no other way for databases to see each other.

If those were two different users (schemas) in the same database, you'd simply GRANT SELECT and enjoy.

Perhaps your company will have to reconsider allowing you to use database links. Or, at least, allow it once so that you (or DBA) could create a link, and then revoke CREATE DATABASE LINK (but it'll exist and you'll be able to use it).

Improve this answer

4 Comments

Thank you for your answer! But just another small question: Could this work, when I just create and host a new, local oracle database and set up a database link from there to the remote databases, or do both ends need DATABASE LINK privileges to get this to work?
@Robama, yes set up a local DB (it could be XE) and define from there two DB links to the target databases. You will use the same credentials as in the SQL Developer. You may then join tables from both sources via DB link (assuming they are not extreme large to be transfered to the client).
Right; that would work, but think security. If The Company doesn't allow DB links, that's probably for a reason. I wouldn't want you to get fired because you managed to find a workaround.
Thank you for your answers! @Littlefoot: We work with completly sealed off testing environments with a db clone from its productive system, filled with dummy data entries only. It is ok with the management, only because we do end2end-based testing, we may not alter the database structure itself. but any kind of read-only access to it is fine. Cheers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.