Automatic database links in SQLDeveloper

April 7, 2014


People invent really complicated ways to achieve simple tasks such as copying a table from one database to another. With database link (which is arguably RDBMS’s the most under appreciated feature) it is just one SQL command:

create table emp as select * from scott.emp@dblink1;

But who has time to create database links? In SQLDeveloper 4.0 you don’t have to. Assuming that you have the right connection the script

set autodblink on
create table emp as select * from scott.emp@connection1; -- assuming there is connection "connection1"

would create temporary db link, then run the statement and, finally, drop the link.

Another example, which benefits from this functionality is comparing a table in two different databases:

set autodblink on
(select * from employees@connection1 
minus
select * from employees)
union
(select * from employees
minus
select * from employees@connection1);

Watch out for pitfalls: if connection info doesn’t store a password, you won’t be asked for it — the fix is scheduled for 4.0.2 patch.

About these ads

3 Responses to “Automatic database links in SQLDeveloper”


  1. Doesn’t seem to work for me. I am using version 4.0.1.14.
    Running the following gives ORA-02019: connection description for remote database not found. Also if the connection has any special characters, for example a connection named xe-sys, I get a ORA-00933: SQL command not properly ended error.
    set autodblink on
    create table emptest as select * from hr.employees@xesys;


  2. I have the same issue with the ORA-02019. I’m on 4.0.2.15


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: