Autonomous Database or not

As more and more customers are moving their databases to Oracle cloud, they have a mix of on-premises, Oracle database cloud services and Oracle Autonomous database in their environments. DBAs and developers usually use sql scripts to automate several task in the database, in some cases, they may want to execute some tasks only on the Autonomous and others only on non-Autonomous database. It is always a good practice to have just one version of scripts to maintain consistency and avoid the maintenance overhead of different versions, customers often ask how to determine from within SQL if we are running on Autonomous database or not? and then based on that execute a specific SQL or PL/SQL code or not
 
The environment used in the demo are

• Oracle Autonomous database on Shared Infrastructure
• Oracle database running on-premises database.

 
The following SQL will help us to identify Autonomous database.
 
rajesh@ATP19C> select cloud_identity from v$pdbs;
 
CLOUD_IDENTITY
----------------------------------------------------------------------------------------------------------------------
{
  "DATABASE_NAME" : "ORA19C",
  "REGION" : "us-phoenix-1",
  "TENANT_OCID" : "OCID1.TENANCY.OC1..AAAAAAAAIGT4UTAJPQLCW7OFSUZ6M33BUFL3NREM3KK6MHRYZOZDCYJFVTMA",
  "DATABASE_OCID" : "OCID1.AUTONOMOUSDATABASE.OC1.PHX.ANYHQLJSH7AFZ4IAJ5XHAINXEBQD42BFJ73K46PP5HQACM36FE7D4ITXWJHQ",
  "COMPARTMENT_OCID" : "ocid1.compartment.oc1..aaaaaaaaeqlrjzpcjmjeku6e2qlqjnotcurkuvcgwtrdcmu3zh3hensrhqra",
  "OUTBOUND_IP_ADDRESS" :
  [
    "129.146.198.199"
  ],
  "PUBLIC_DOMAIN_NAME" : "adb.us-phoenix-1.oraclecloud.com",
  "TENANT_ACCOUNT_NAME" : "xxxxxxxxxx",
  "AUTOSCALABLE_STORAGE" : false,
  "BASE_SIZE" : 1099511627776,
  "INFRASTRUCTURE" : "Shared",
  "SERVICE" : "ATP",
  "APPLICATIONS" :
  [
    "GRAPH",
    "OMLMOD",
    "ORDS",
    "APEX",
    "OML",
    "ODI"
  ],
  "AVAILABILITY_DOMAIN" : "iRbP:PHX-AD-1"
}
 
By looking at the OCID that includes the string “AUTONOMOUSDATABASE” we can conclude it is an Oracle Autonomous database
 
The cloud_identity column will have null values for on-premises database.
 
demo@PDB1> select cloud_identity from v$pdbs;
 
CLOUD_IDENTITY
--------------------
 
 
demo@PDB1>
 
 
to determine the workload type of the Autonomous database (following flavours of autonomous databases are currently available  a) Autonomous Transaction processing (ATP) b) Autonomous Datawarehouse (ADW) c) Autonomous JSON database (AJD) d) Autonomous APEX (APX) ) using the SQL
 
query the “cloud_identity” column and look for SERVICE attribute.
 
rajesh@ATP19C> select json_value(cloud_identity,'$.SERVICE') workload_type
  2  from v$pdbs t;
 
WORKLOAD_T
----------
ATP
 
Additionally, the cloud_identity column of V$PDBs provide more information about Autonomous database, like Infrastructure type (shared/dedicated Exadata infrastructure) , availability domain (where the Exadata infrastructure resides), outbound IP Address (to white list if any)
 
rajesh@ATP19C> select json_query(cloud_identity,'$.OUTBOUND_IP_ADDRESS' returning varchar2(30)
  2              with conditional array wrapper) outbound_ip,
  3      json_value(cloud_identity,'$.INFRASTRUCTURE' returning varchar2(10) ) as  infra_type,
  4      json_value(cloud_identity,'$.AVAILABILITY_DOMAIN' returning varchar2(20) ) as  domain_availbale
  5  from v$pdbs t;
 
OUTBOUND_IP                    INFRA_TYPE DOMAIN_AVAILBALE
------------------------------ ---------- --------------------
["129.146.198.199"]            Shared     iRbP:PHX-AD-1
 
 
The above queries accessing v$pdb, need a special privilege either an explicit grant or SELECT_CATALOG_ROLE to be granted. For non-privileged users we can make of SYS_CONTEXT function calls like this
 
rajesh@ATP19C> select sys_context('userenv', 'cloud_service') as cloud_service from dual;
 
CLOUD_SERV
----------
OLTP