Oracle DB : Create Database Link to access objects from a remote database.

Most often we have the databases on different servers. the transaction database and the traditional data-warehouse will always reside on different servers. Sometimes we might have to use the database objects from OLTP to load data into OLAP. This is most common when we use the Incremental Loads or Materialized views and the most common technique is to create a database link to enable users to access the objects from remote database.


Create DB Link

        CREATE PUBLIC DATABASE LINK 
        [LINK NAME]
        CONNECT to [USERNAME]
        IDENTIFIED BY 
        [PASSWORD]
        USING '[TNS NAME]'

for example if i have to connect to database on server myserver01 with DB details as below

Scott/tiger @ ORCL , orcl being the tns entry.

        CREATE PUBLIC DATABASE LINK
        MY_LINK
        CONNECT TO SCOTT
        IDENTIFIED BY tiger
        USING 'ORCL'

we can add the tns entry to tnsnames.ora file under location [DB_HOME]/NETWORK/ADMIN/


Now you can try accessing the object of remote database from your database by adding @DBlink at the end.

For example if you want to access employee table as per the above we use below statement.

        SELECT * FROM EMPLOYEE@MY_LINK

Oracle DB : Finding the DB version and Other important details

Its pretty common for us to check the DB version and sometimes the parameters that help in most of the activities that we do on a daily basis below are some that might help.


Database Version:


for just the version number 

        SELECT version FROM V$INSTANCE;

For version details of other tools of Database 

        SELECT * FROM V$VERSION;

NLS Parameters

For all the NLS parameters of database 

        SELECT * FROM V$NLS_PARAMETERS;

For Just Language , Region and Character Set

        SELECT DECODE(PARAMETER, 

        'NLS_CHARACTERSET',  'CHARACTER SET',

        'NLS_LANGUAGE', 'LANGUAGE',

        'NLS_TERRITORY', 'TERRITORY') NAME,

        VALUE FROM V$NLS_PARAMETERS

        WHERE PARAMETER IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY');

Linux : Find Process Running on a Specific Port and Kill it

This is my first post here and i don't want this one to be very much complex so that I have a chance to  mess this up and you feel like I am muddling things up. Its pretty simple and yet most useful to many. Very often we see issues on Linux which are related to port numbers.

Lets just say it like this, the most common error related to ports is "port already in use by some other process". It can be OBIEE, Informatica or SOA or anything. Because of this sometimes you are not able to start service of applications which is very annoying.

Lets first fine what is using this port. on Linux (mine RHEL) its this way.

               netstat -plten |grep 6051

Once we find the process we can get the process id from the last section like from the above picture it is xxxxxx/java. Now lets just kill the process using below command.

                   Kill -9 17310

That's it you are done , you can now rerun your command which resulted in the port already in use error.