Alternative to Oracle Database Links
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?
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?
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.
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.
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.
ASKER
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.
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.
ASKER
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?
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?
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.
>>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.
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Yes, this is exactly what i want. I'll update you in a couple of days.
ASKER
Great!
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.