Alternative to Oracle Database Links

judejames
judejames used Ask the Experts™
on
When i connect to Oracle using database links in SQL Developer, I can't get Packages and other objects of the remote database on the left console tree. What can be done that when I connect to remote database I get the procedures & packages of the remote database in SQL Developer console tree.
Looks odd, right?
Actually, there is a firewall between the remote database, and access is available only to one host on our end. How can users on our end access the remote database seamlessly? I know this scenario requires a diagram, but if anybody could understand, please comment.
Do you think we should do some kinda ip forwarding or remote listener thingy?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
I don't think it has anything to do with listener. You are connected to the DB.

Are you sure you are logged into the correct schema? Are the packages you are looking for in another schema? If so, they won't show by default.
johnsoneSenior Oracle DBA

Commented:
I do not believe what you are doing is possible.  I don't know a tool that can access objects across a database link.  They all work on the local dictionary.

You should be able to connect to the target of the database link and view it.  Otherwise, I believe your only choice is to select from ALL_SOURCE to see the code.
johnsoneSenior Oracle DBA

Commented:
I just opened a connection in SQL Developer.  I logged in as a DBA privileged user and I can see the details of the public database link, but I cannot follow it.  If there is a tool out there that will do it, I don't know of it.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Okay folks I know if we have some sort of REMOTE LISTENER which we can register with the remote oracle server on our end, it might work. But the question is: Can we use a remote listener in a single instance environment as we dont' have an RAC.
Top Expert 2009

Commented:
To a given database login, a remote link is just another object. "Following it" requires a connection to the next db.  You can write synonyms and views against the remote objects, but as johnsone said, those objects do not exist in the catalog of the current database you are connected to. I've never seen this functionality and it has nothing to do with RAC or listeners. You are correctly connected to the db which means the listener is working fine.

Author

Commented:
Thanks for your comments mrj. study remote listeners with RAC. Can somebody tell me if this is possible with single instance also?
i want to have a remote listener on the host housing the oracle database (at our end) which has permissions to access the remote database. This way the remote database will have two listeners. And our users can then tnsping the listener of our database which will indeed be remote listener of the remote database.
Makes sense?
johnsoneSenior Oracle DBA

Commented:
But that has nothing to do with being able to access or see the objects through a database link.  Where the listener resides does not give you any different access to the objects within the database.
Top Expert 2009

Commented:
>>study remote listeners with RAC

What do you want me to study? I've personally built RAC clusters.


1) It has nothing to do with your original question.

2) It has nothing to do with RAC. Plus you are not using RAC.

3) A RAC instance is still an single database. A remote database link is to a completely different database.

4) A remote listener is a load balancing mechanism. Not some "database merging" mechanism.

Sorry I am not following you.

Author

Commented:
I want my users to have access to remote database, but not through database links. Can we add a remote listener on our database for the remote database. So when they connect to the remote listener on our database, they get redirected to remote database? This way we have a packages on sql developer, and no need to create database links for remote db. Because actually the users will be using the remote listener on our subnet to connnect to remote db on another subnet. Mind you our users only have access to our database server and host. They can not see the remote database, ping or tnsping it.
Top Expert 2009
Commented:
I think I understand you now, you essentially want a proxy connection where now you don't have direct access to the 2nd db.


                                           | firewall
you & your users --------------------------------- db 1 ---------------------- db2
                                           |


And db2 is what you want a direct connection to.

Top Expert 2009

Commented:
Assuming the diagram above describes your scenario, you can configure Oracle Connection Manager, or a remote listener on db1 or some other node inside the firewall. So if you don't have access to configure any of the systems inside that firewall, you cannot accomplish this.

If you do have admin privs on both boxes, you can use Connection Manager or Remote Listener.

Try this:

http://oracledocaccess.blogspot.com/2009/04/remote-listener.html

Author

Commented:
Yes, this is exactly what i want. I'll update you in a couple of days.

Author

Commented:
Great!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial