From a database, we can create a database link to another remote database such as one on Amazon EC2 virtual server as shown below. Here ec2-12-34-567-899.compute-1.amazonaws.com is the amazon EC2 Linux instance's Public DNS.SQL> create database link dl_aws_ec2 connect to prod_DB identified by PWDXXX using '(description=(address=(protocol= TCP)(host=ec2-12-34-567-899.compute-1.amazonaws.com)(port=1521)) (connect_data=(sid=XE)))';
Database link created.
SQL> select count(1) from user_tables@dl_aws_ec2;
Just like we query any databases, we can see a few tables with names starting with the word "DEMO", count the number of records, and if we want, make a local copy of the tables.SQL> select table_name from user_tables@dl_aws_ec2 where table_name like 'DEMO%' and rownum <5 order by table_name;
SQL> select count(*) from DEMO_CUSTOMERS@dl_aws_ec2;
SQL> create table DEMO_CUSTOMERS_LOCAL as select * from DEMO_CUSTOMERS@dl_aws_ec2;