BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

EnterpriseDB: Oracle Database Links
Oracle Tips by Burleson

Another area of concern may be database links, particularly if you want to maintain links between Oracle and EnterpriseDB. You may use Oracle's Heterogeneous Gateway for connections from Oracle to EnterpriseDB. This is a standard configuration and is well documented.

Creating a link from EnterpriseDB to Oracle requires database link support inside EnterpriseDB. That support is currently an add-on and does not work exactly like database links in Oracle. The syntax differences are fairly major and you will not be joining local and remote tables in a single step.

However, having said that, you can use the available functionality to create a link and pull data locally where you can use it to join to other tables. If you are just pulling data from the remote database, it is easy to use and configure. The steps are documented in the file README-DBLINK_ORA_SETUP.txt in your

$EDB_HOME\dbserver\lib\postgresql\contrib. directory. I am reproducing the instructions below. These are valid up to v8.1.5. If you are using a later version, check your documentation before using.

Please perform the following steps for setting up dblink_ora contrib module:

For Linux Users:
===============

All steps described below should be performed using the DBA user (default is enterprisedb). Only restarting the database server requires root login.

1. Open the .bash_profile file for enterprisedb user. This is a hidden file located at /home/enterprisedb.

2. Append to this file the following line:

export LD_LIBRARY_PATH=$EDB_HOME/dbserver/lib/postgresql/contrib

3. Restart the database server by executing the following two commands:

i. /etc/init.d/edb_8.1 stop
ii. /etc/init.d/edb_8.1 start

4. Open edb-psql and run the sql script 'dblink_ora.sql' located at $EDB_HOME/dbserver/lib/postgresl/contrib/. This script creates all required functions for dblink_ora.

5. After successful completion of the script, you can start using dblink_ora.

For Windows Users:
=================

1. Copy the 'oci.dll' file from $EDB_HOME\dbserver\lib\postgresl\contrib\ to $EDB_HOME\dbserver\bin\.

2. Open EDB-PSQL and run the sql script 'dblink_ora.sql' located at $EDB_HOME\dbserver\lib\postgresl\contrib\. This script creates all required functions for dblink_ora.

3. After successful completion of the script, you can start using dblink_ora.

EXAMPLE OF USAGE:
================

SELECT dblink_ora_connect (
    EDB_CONN_NAME,
    ORA_HOST,
    SERVICE_NAME,
    USER,
    PASSWORD,
    ORA_PORT);

SELECT dblink_ora_status (EDB_CONN_NAME);

SELECT *
FROM dblink_ora_record(
EDB_CONN_NAME,
'SELECT id from OracaleTable') AS t1(id VARCHAR);

Contrib modules are modules that are contributed to the PostgreSQL project by hackers (and I mean that in a good way) who freely improve PostgreSQL. Many good things that are currently a part of PostgreSQL (and hence EnterpriseDB), started life as a contrib module.

I will now walk through an example of this. I have an Oracle database running locally. The service name is ORCL2 and it is running on port 1521. I will connect to user HR who uses HR as the password.

After copying the oci.dll as directed above for Windows users, I copied the file oci.dll to my EnterpriseDB dbserver\bin directory. I also ran the dblink_ora.sql script that also resides in the contrib. directory. This file creates a table and several functions that are used by the DB Link routines.

To configure the link, I run this command in SQL Interactive:

SELECT dblink_ora_connect(
       'hr_orcl',
       'localhost',
       'orcl2.world',
       'hr',
       'hr',
       1521);

The result should be an "OK". If you get an error that the connection could not be created, verify your database domain (the .world portion must be included unless your sqlnet.ora contains a default domain) and verify your port.

To use the query, run a select. You will run this to open and populate data from the link. This step creates a virtual table that you can join to other tables. First, I will show you stand-alone and then I will show you with a join.

SELECT * FROM dblink_ora_record(
      'hr_orcl',
      'SELECT first_name, last_name, job_id from employees') AS
   virt_table(first_name VARCHAR, last_name VARCHAR, job_id VARCHAR);

The result of this query will be a list of rows, specifically the rows identified in the query. You may join this list of values to a table just as you would a view. I have previously loaded the JOBS table from the Oracle HR schema into the EnterpriseDB database. The query below is joining the local JOBS table to the remote EMPLOYEES table.

SELECT first_name, last_name, job_title
FROM (
     SELECT * FROM dblink_ora_record(
      'hr_orcl',
      'SELECT first_name, last_name, job_id from employees') AS
   virt_table(first_name VARCHAR, last_name VARCHAR, job_id VARCHAR)) vt
JOIN jobs
ON vt.job_id = jobs.job_id

The result from this query should look something like this:

When you create the link as above, it automatically opens the connection. In the future, to reuse a database link, you need to create it and you will need to manually open the connection before using it. You may do that with:

SELECT add_remote_link(
       'hr_orcl_perm',
       'O',
       'localhost',
       1521,
       'hr',
       'hr',
       'orcl2.world');

To check the status of the link, run:

SELECT dblink_ora_status('hr_orcl_perm');

The status should be "Unavailable" until the link has been opened. To open the link, run:

SELECT dblink_ora_connect('hr_orcl_perm');

The status response to an open link should be "OK".

You may close the link by calling:

SELECT dblink_ora_disconnect('hr_orcl_perm');

You may also perform DDL and DML using the dblink_ora_exec function. In the example below, I am creating a table and then a procedure into that table in the Oracle database:

select dblink_ora_exec(
'hr_orcl',
'CREATE TABLE msgtxt (msgtxt VARCHAR2(30)) ' );

select dblink_ora_exec(
'hr_orcl',
'CREATE OR REPLACE PROCEDURE msg_txt(p_recs IN VARCHAR2) ' ||
'AS BEGIN ' ||
'FOR I IN 1..p_recs LOOP ' ||

'INSERT INTO msgtxt (msgtxt) VALUES (''Hello World!''); ' ||
'END LOOP; ' ||
'END;');

On success for all of these calls you should see EnterpriseDB reply with an OK.

I can call the newly created procedure using the dblink_ora_call function. This is the function to call procedures. You cannot return values using this function.

select dblink_ora_call (
'hr_orcl',
'BEGIN msg_txt(5); END;',
1 );

I passed the parameter 5 to my procedure so when I select from the msgtxt table, I expect to see 5 records.

SELECT * FROM dblink_ora_record (
      'hr_orcl',
      'SELECT msgtxt from msgtxt') AS
   virt_table(msgtxt VARCHAR);

If I want to call a function, I would use dblink_ora_record. I will create the function with dblink_exec and call it with dblink_ora_record.

select dblink_ora_exec(
'hr_orcl',
'CREATE OR REPLACE FUNCTION helloworld RETURN VARCHAR2 ' ||
'AS BEGIN ' ||
'RETURN ''Hello World!''; ' ||
'END;');

I have been using virt_table as my virtual table name but that is just my preference. You may replace virt_table with any table name you like.

SELECT * FROM dblink_ora_record (
      'hr_orcl',
      'SELECT helloworld from dual') AS
   virt_table(msgtxt VARCHAR);

You may use the dblink package in EnterpriseDB to achieve almost anything you can achieve with Oracle style database links. The approach is slightly different but the results are the same.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books

EnterpriseDB: Oracle Database Links Oracle Tips by Burleson

EnterpriseDB: Oracle Database Links
Oracle Tips by Burleson