What are the different types of database links in Oracle, and how do they work?

There are several types of database links in Oracle:

1. Private database link: This type of database link is created and owned by a specific user and is visible only to that user. It can be used to access objects in another database that the user has permission to access.

2. Public database link: This type of database link is created and owned by a database administrator (DBA) and is visible to all users in the database. It can be used by any user to access objects in the remote database that the DBA has granted permission to access.

3. Global database link: This type of database link is created and owned by a database administrator and is visible to all users in all databases in the same network. It can be used to access objects in a remote database that the DBA has granted permission to access.

4. Shared database link: This type of database link is created and owned by a specific user, but is made available to other users in the same database. It can be used to access objects in a remote database that the user has permission to access.

Database links work by creating a connection between two databases, allowing objects in one database to be accessed from the other database. When a query or transaction is issued that references an object in a remote database, the local database uses the database link to establish a connection to the remote database and retrieves the necessary data.

For example, let’s say we have two databases: DB1 and DB2. We want to access a table called "customers" in DB2 from DB1. We can create a public database link in DB1 that connects to DB2 and grants access to the "customers" table. Then, when we issue a query in DB1 that references the "customers" table, the local database will use the public database link to establish a connection to DB2 and retrieve the necessary data.

Here’s an example of creating a database link in Oracle:

CREATE DATABASE LINK db2_link CONNECT TO db2_user IDENTIFIED BY password USING 'db2_tns';

This creates a database link called "db2_link" that connects to a remote database using the "db2_tns" service name, and authenticates using the username "db2_user" and password "password".

Once the database link is created, we can use it to access remote objects in our queries and transactions, like this:

SELECT * FROM customers@db2_link;

This retrieves all rows from the "customers" table in the remote database connected by the "db2_link" database link.