Is transferring through a database link in Oracle 10g compressed? Is it possible?

Question

I transfer data from one database to another through database links (using INSERT INTO SELECT ... ).

I want to know if the data passed by reference is compressed or can be compressed to avoid overuse of the network. I have very little bandwidth, and I think it will help if it is not already done.

Answer 1

There are some de-duplication , but without serious compression.

There is a UTL_COMPRESS function, but it would be difficult to get it for unpacking at the destination (perhaps a trigger or instead of a view - but this is inconvenient).

EXPDP can use the database link ( NETWORK_LINK ), and in 11g compression , but this requires advanced compression .

Finally, there is the usual extract, zip code, transfer, unpacking, loading

In 11gR2, you can use external tables with a preprocessor for unpacking so you can semi-automate this final option.

Answer 2

As @Gary says, not initially, but you can get compression using the SSH tunnel, assuming you have command line access. The SSH manual page notes that compression can slow down a fast network, but this compromise may be worth it if you are very limited in bandwidth; and you may need to experiment with CompressionLevel in ssh_config to get the best results for your situation.

For example, if your existing link is defined to connect to remote_server port 1521 :

 create database link direct connect to usr identified by pwd using 'DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=remote_server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=remote_service)))' 

You can create an SSH tunnel using a free local port, with something like:

 ssh -C -N -L1522:localhost:1521 remote_server 

And then you can have a DB link pointing to the local side of the tunnel:

 create database link direct connect to usr identified by pwd using 'DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)) (CONNECT_DATA=(SERVICE_NAME=remote_service)))' 

So you just change the host and port. If your existing link uses the tnsnames entry, you can simply change it instead to specify localhost:1522 instead of remote_server:1521 .

Of course, you must make sure that the SSH link does not work when you use the DB link. If this happens, you will get ORA-12541: TNS:no listener error, since nothing will be listened to on your local port 1522.