I have an SQL script that connects to another database using "create database link", and copies some data.
Should this database base link be dropped or closed after I am done using it? (Say, assuming that I probably won't be needing it again).
What's the best practice?
I am hearing some conflicting answers on my team.
If I should be closing it, how do I ensure that the link gets closed when I call:
drop database link somelink;
(I get errors complaining that I have connections open, which is odd because I'm not performing any asynchronous operations/jobs).
From a best practices standpoint, I would be very wary about creating database links dynamically in a script. Database links should be just like any other object-- they should be created once and referenced multiple times. If this is truly a one-time event where you are extracting data from another database before it is decommissioned, creating and dropping the link would make sense. For anything else, I would create the link and let it remain for the next person that may need it unless there is a compelling need to drop it (i.e. you're getting an exception from the security group to create the link for a short period of time).
You can close a database link with the command
ALTER SESSION CLOSE DATABASE LINK <<dblink name>>
which is probably a nice thing to do when you're done with it and before you drop it. That should eliminate the errors you're getting when you drop the link.