Tips & Tricks 5 : Insert data over dblink from partition

Inserting data over a dblink from specified partition gave following error,

insert into testtbl select * from testtbl@TESTLINK partition (p1);

ORA-02070: database TESTLINK does not support extended partition name in this context

Briefly , partition clause is not supported over the dblink.In order to solve this issues , view is created at remote site that uses partition clause

Remote site : create view testtblp1_v as select * from testtbl partition (p1);

Local site    :  insert into testtbl select * from testtblp1_v@TESTLINK;

Foreign Data Wrapper for PostgreSQL Part 2 : Create Server , User Mapping and Foreign Table

After installation of foreign data wrapper which can be read in this blog post , in order to access data from PostgreSQL database we must create server, user mapping and foreign table.

Creation of server provides connection information of foreign data resource that foreign data wrapper uses to access.

create server oracle_SERVER foreign data wrapper oracle_fdw options (dbserver '//10.1.X.X/DB_INSTANCE_NAME');

Next step which is creation of user mapping contains a mapping of user to a foreign server.

create user mapping for $POSTGRESQL_USER server oracle_SERVER options(user 'oracle_user',password 'oracle_user_password');

$POSTGRESQL_USER is the PostgreSQL database user which is mapped to foreign server.

Foreign server owner can create user mapping for any user in server.Also a user which has usage privilege for foreign server can create user mapping for his own user.

Finally,foreign table is created with following command,

create foreign table foreign_table (user_id numeric,user_name text) server oracle_SERVER options (schema 'oracle_schema_name',table 'oracle_table');

With psql interface you can select foreign_table.

user_id and user_name are table fields which are identical to Oracle table

Source :
http://www.postgresql.org/docs/9.1/static/sql-createserver.html
http://www.postgresql.org/docs/9.1/static/sql-createusermapping.html
http://www.postgresql.org/docs/9.1/static/sql-createforeigntable.html

Installation of Oracle Foreign Data Wrapper for PostgreSQL

If you are working on both Oracle and PostgreSQL databases , sometimes you need to access each database from other. My project contains accessing Oracle database from PostgreSQL database and this took me to search how to do it and found foreign data wrappers.Foreign data wrapper based on accessing to data that is not in PostgreSQL database.

Following link contains all FDW information for PostgreSQL .

https://wiki.postgresql.org/wiki/Foreign_data_wrappers

In order to install Oracle FDW , below Oracle Instant Client rpm files and oracle_fdw extension are needed.

Oracle FDW - https://github.com/laurenz/oracle_fdw

Oracle Instant Client - http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

Install oracle-instantclient-basic, oracle-instantclient-sqlplus, oracle-instantclient-devel packages for your version.

After installation below envinroments are set.

export PATH=$PATH:/usr/pgsql-9.3/bin
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin:$LD_LIBRARY_PATH

Extract downloaded oracle_fdw.master.zip file and change into directory.Software installation is done with following commands

$make

$make install

Now oracle_fdw shared library has been installed in the PostgreSQL library directory and oracle_fdw.control and the SQL files are in the PostgreSQL extension directory.

Following step is done to install extension in a database with superuser.

$psql -d {Your database name}

$CREATE EXTENSION oracle_fdw;

ERROR:  could not load library "/usr/pgsql-9.3/lib/oracle_fdw.so": libclntsh.so.11.1: cannot open shared object file: No such file or directory

  

Above error can be seen while creating extension because of library missing which is actually exists.In order to solve this error, Oracle library path is added to system library list.

$echo /usr/lib/oracle/11.2/client64/lib> /etc/ld.so.conf.d/oracle.conf

$ldconfig

Executing same command for install extension completed successfully after library arrangement.

$CREATE EXTENSION oracle_fdw;

CREATE EXTENSION

enq tx - row lock contention tx mode 4

After transition of new project to live system, we have seen excessive "enq tx - row lock contention" wait event on 2 node RAC system.From top activity as seen below ,

Node 1 :

Node 2 :

 

these waits cause users not work properly. When examine the sql which causes lock wait was simple insert statement and from P1 field of v$session who waits for this event indicates that lock type was  shared mode 4 TX wait. Common causes for mode 4 are 

a.Unique index

b.Foreign Key

c.Bitmap index

  

v$session , for sessions which wait for this event gives object which cause wait.

select row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#

  from gv$session

where event='enq: TX - row lock contention' and state='WAITING'

Output of sql gave me object which was bitmap index and after dropping index  problem has gone away.According to explanation for this type of operation , each entry in bitmap index can cover multiple rows   in table.If two sessions would like to update rows covered by the same index key entry , second session waits for the first session's transaction to be performed by commit or rollback by waiting for the TX lock in mode 4.