Database Links

I need to create a lot of test data for unit testing some new code. Now I already had some good data in another database. So I used a tool to create some scripts that insert the data into the new database. There were some problems with this technique. My database tables have a lot of columns in them. The creation of insert scripts by the tool puts the whole insert statement on one line. This line turns out to be much too long for SQL*Plus to process. So I started to manually break up the lines. There had to be a better way.

Database links came to the rescue. You create the objects by specifying which database to connect to. You also need to provide login credentials when creating the link. Then you can perform queries that access the tables from the other database through the link. This was awesome. The link itself becomes an object in your schema. I hear you can even connect to non-Oracle database. I have no need for that feature though.

You do need to make sure the remote database you are linking to is in the tnsnames file on your server. Remember to do a COMMIT after you run the query through the link to release the rollback segment in the other database. The link operates like a pointer. If you really wanted to, a link can be used as a poor man's replication technology. If you want to get some information on your links, check out the DBA_DB_LINKS view.