I am doing this. I created a database link, then a synonym to the table I needed, then created the table from my prod database to my development database. I have some questions about this. What is the difference between the synonym and database link? Having a synonym and database link does seem to cause problems though. I have to reset my password every 30 days. I often forget about the synonym and database link. So this is causing me to lock by databases then I have to bug the DBA’s to unlock my databases. Should I drop both the database link and synonym, should I drop just the synonym, should I drop just the database link? Are they needed after I create the database? I only drop and recreate the table once a year because there are plenty of records to work with after I create the table.
CREATE DATABASE LINK my_db_link CONNECT TO user IDENTIFIED BY passwd USING 'database_name';
CREATE SYNONYM my_table FOR my_table@my_db_link;
SELECT * FROM my_table;
create table my_table as (select * from my_table@my_db_link);
The way you put it, you do not need a synonym.
- create database link
- it lets you establish a tunnel from your database to some other database which is in the same network
- use CTAS to create table in your schema by selecting all rows from table over the database link
Doing so, you’d have a “copy” of the remote table in your schema and you can work with it as long as you want. It doesn’t depend on possible changes on the remote side (whether remote user’s password has changed, was locked, dropped … doesn’t matter. Data is in your own table).
Next year, you can repeat that process (and ask DBA for the new password only once a year).
Your synonym plays no part in this story. That’s just an alias, and hence a preference that doesn’t change the semantics or how things are working beneath the covers. Whether you use a synonym or not, the issue is entirely focused on the database link, which has to embed in itself an authenticating function to gain access to a remote database.
Since you are being forced to change the password of the connect account (the remote user specified by “CONNECT TO [username]“) every 30 days, you need to reduce the burden of password management.
First, you could simply update the password by using the ALTER DATABASE LINK [linkname] CONNECT TO [username] IDENTIFIED BY [password]. There’s no need to drop and recreate the link as a lot of folks do. Oracle has helpfully provided the ALTER functionality to handle credential change. However it does require you to respecify the remote user (CONNECT TO [username]) even if you’re not changing it. It’d be nice if they would let you leave that out, but as of the present time you have to include it in order to change the password.
However, even better than this is to avoid using database links that specify credentials (CONNECT TO [username] IDENTIFIED BY [password]). If your remote user and your local user (the owner of the link) are the same username and have the same password then you don’t need a password. You don’t even need a username. Create a link without any credentials specified:
CREATE DATABASE LINK [linkname] USING '[tns string or alias]'
Note that I have no credentials (CONNECT TO [username] IDENTIFIED BY [password]) in my link DDL. With such a credential-less link, Oracle will always attempt to connect to the remote account of the same username as the local/link-owner and will authenticate if their passwords match. So as long as you keep in sync the password used by both local and remote accounts and the two accounts are named the same, the link will work and you won’t ever need to ALTER the link due to password changes.