Getting the instance name on an Oracle database (and somehow, the PostgreSQL counterpart)

In an Oracle database, the connection URI contains a so called instance name, a way to discriminate somehow the Oracle engine to which you are connecting to. I am not an Oracle guru (and quite frankly, I don’t want to be!), but this is somehow similar to how PostgreSQL databases work (in a bird’s eye view): when you connect to a PostgreSQL database you are connecting to an engine (a daemon) that is serving the database. When you connect to an Oracle instance, you are connecting to an engine (a daemon) that is serving such engine.
A big difference between the two approaches is that Oracle does not provide a per-databases isolation, as PostgreSQL does.
Let’s see an example of URIs, starting from PostgreSQL first: 

postgres://luca@miguel/testdb
where:
  • postgres is the protocol;
  • luca is the username; ` miguel is the remote machine;
  • testdb is the database instance.

And here’s the Oracle counterpart: 

luca[testdb]@miguel/service


There’s an added value service which is the name of the instance to which you want to connect to.
It goes something like this: the Oracle instance is the same as the PostgreSQL daemon serving a PGDATA: each one do serve a bunch of databases where PostgreSQL managed the latter as separated entities and Oracle does not.

Why is it important to be able to discriminate the instance name?
One thing I see very much is that the instance name is used to discriminate between the set of databases for production and for staging.
Of course, some other logic can be in place.
Luckily, Oracle has the v$session internal catalog that can be used to get the column (you guess) instance_name: 

SELECT instance_name
FROM v$instance;


In my Java applications I do something like the following to discriminate between different services: 

SELECT
  CASE WHEN instance_name like '%production%' THEN 1
  ELSE  2
  END
 FROM v$instance;


Be aware that Java will probably manage the return value as a BigDecimal!


What about PostgreSQL? Well, being the internal organization of services and databases different, PostgreSQL does not have anything like the service name. However, there’s a couple of configuration parameters that can be used to “simulate” something like the service instance:
  • cluster_name a string that is used, also, to distinguish between operating system backend processes;
  • update_process_tile a boolean toggle that is used to update the backend operating system process title.

It is possible to use the cluster_name value as an indicator of which cluster (i.e., set of databases) you are connected to with a simple query: 

SELECT CASE WHEN current_setting( 'cluster_name' ) LIKE '%production%' THEN 1
ELSE 2
END;


that is pretty much the same of the above Oracle query. The idea here is to get the cluster_name string out of current_setting(), therefore querying PostgreSQL current_setting() is pretty much the same as querying Oracle v$session.instance_name.

The article Getting the instance name on an Oracle database (and somehow, the PostgreSQL counterpart) has been posted by Luca Ferrari on March 30, 2022

Tags: oracle , postgresql