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;
| COUNT(1) |
|---|
| 15 |
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;
| TABLE_NAME |
|---|
| DEMO_CUSTOMERS |
| DEMO_ORDERS |
| DEMO_ORDER_ITEMS |
| DEMO_PAGE_HIERARCHY |
SQL> select count(*) from DEMO_CUSTOMERS@dl_aws_ec2;
| COUNT(*) |
|---|
| 7 |
SQL> create table DEMO_CUSTOMERS_LOCAL as select * from DEMO_CUSTOMERS@dl_aws_ec2;
Table created.
