oracle-databaseoracle-cloud-infrastructure

How to identify via SQL if the database is running on OCI?

I have a system where the database can be installed in different locations: on AWS (RDS), on OCI (Autonomous Database), or even on a client's own server. I need to identify where the database is installed to facilitate action with N1 support. In an "About" window, I have the database version information. My plan is to concatenate an add-on to the version:

Identifying RDS is possible by checking for the existence of the RDSADMIN package. However, I haven't found a way to identify whether Oracle is on OCI in a similar way.

Solution

  • Some context values may help you. For example, SYS_CONTEXT ('USERENV', 'ORACLE_HOME') will return "/rdsdbbin/oracle" in a RDS instance.

    EDIT: A database in the Oracle infrastructure typically has a schema called "C##DBLCMUSER". A database in Amazon's infrastructure (RDS, specifically) has a schema called "RDSADMIN". So, the SQL below is close to the result you want.

    SELECT CASE
         WHEN COUNT (CASE
                       WHEN USERNAME = 'C##DBLCMUSER' THEN 1
                     END) > 0 THEN 'OCI'
         WHEN COUNT (CASE
                       WHEN USERNAME = 'RDSADMIN' THEN 1
                     END) > 0 THEN 'RDS'
       END
  FROM DBA_USERS