How To Creat a Link To a Remote Database
Oracle allows you to connect different databases with each other. This is possible even if the database is located on different servers. Do not forget that access to the remote server will be made at the time of the query, and this, in the presence of slow links between servers, can seriously slow down your query.
- Create a link (link) to another database:
create public database link mylink connect to user identified by password using 'db01';
- mylink – the name of the link, which we want to use;
- user – user that can make a connection to an external database;
- password – the user’s password;
- ‘db01’ – a connection string, which should be necessarily (!) in single quotes. Just be sure to presence of this string in tnsnames.ora.
- Create a synonym (synonym):
create public synonym rtable for user.ltable@mylink;
- rtable – the local name for a table on a remote server;
- user – circuit on the remote server that contains the table ltable;
- ltable – a table on a remote server;
- mylink – the name of the link created in the previous step
- After completing the previous steps, you can query the table located in a remote database, as follows:
select * from rtable;