How to Retrieve Oracle DB System Details Using SQL?

Hi all,

I hope you’re fine.

You may don’t know, but Brazilians love WhatsApp!

I’m part of a WhatsApp Group for the Brazilian Oracle Users Group (GUOB).

Today, an user posted these questions there:

The questions are:

• Good afternoon, do we have any SQL query to run on the Oracle database to check if it is in the Cloud, a DBSystem?
• 19c <=

That was a great question! I’m glad that I was able to respond and help this guy.

So, I will share two queries that can help you in case you need it.

This one will return more details and not bringing the ssh_authorized_keys and user_data fields.

DECLARE
  l_url          VARCHAR2(4000) := 'http://169.254.169.254/opc/v1/instance';
  l_http_request UTL_HTTP.req;
  l_http_response UTL_HTTP.resp;
  l_response_text CLOB;
  l_cleaned_json CLOB;
  l_buffer        VARCHAR2(32767);
  l_line          VARCHAR2(4000);
  l_pos           PLS_INTEGER;
BEGIN
  l_http_request := UTL_HTTP.begin_request(l_url, 'GET');
  
  UTL_HTTP.set_header(l_http_request, 'Authorization', 'Bearer Oracle');

  l_http_response := UTL_HTTP.get_response(l_http_request);
  
  LOOP
    UTL_HTTP.read_line(l_http_response, l_buffer, TRUE);
    l_response_text := l_response_text || l_buffer || CHR(10);
  END LOOP;

EXCEPTION
  WHEN UTL_HTTP.end_of_body THEN
    UTL_HTTP.end_response(l_http_response);

    SELECT JSON_SERIALIZE(
      JSON_OBJECT(
        'agentConfig' VALUE JSON_QUERY(l_response_text, '$.agentConfig'),
        'availabilityDomain' VALUE JSON_VALUE(l_response_text, '$.availabilityDomain'),
        'canonicalRegionName' VALUE JSON_VALUE(l_response_text, '$.canonicalRegionName'),
        'compartmentId' VALUE JSON_VALUE(l_response_text, '$.compartmentId'),
        'displayName' VALUE JSON_VALUE(l_response_text, '$.displayName'),
        'faultDomain' VALUE JSON_VALUE(l_response_text, '$.faultDomain'),
        'hostname' VALUE JSON_VALUE(l_response_text, '$.hostname'),
        'id' VALUE JSON_VALUE(l_response_text, '$.id'),
        'image' VALUE JSON_VALUE(l_response_text, '$.image'),
        'metadata' VALUE JSON_OBJECT(
            'agentAuth' VALUE JSON_VALUE(l_response_text, '$.metadata.agentAuth'),
            'dbServiceType' VALUE JSON_VALUE(l_response_text, '$.metadata.dbServiceType'),
            'dbSystemShape' VALUE JSON_VALUE(l_response_text, '$.metadata.dbSystemShape'),
            'dbType' VALUE JSON_VALUE(l_response_text, '$.metadata.dbType'),
            'nodeNumber' VALUE JSON_VALUE(l_response_text, '$.metadata.nodeNumber'),
            'numberOfNodes' VALUE JSON_VALUE(l_response_text, '$.metadata.numberOfNodes'),
            'privateIP0' VALUE JSON_VALUE(l_response_text, '$.metadata.privateIP0')
        ),
        'region' VALUE JSON_VALUE(l_response_text, '$.region'),
        'shape' VALUE JSON_VALUE(l_response_text, '$.shape'),
        'state' VALUE JSON_VALUE(l_response_text, '$.state'),
        'tenantId' VALUE JSON_VALUE(l_response_text, '$.tenantId'),
        'timeCreated' VALUE JSON_VALUE(l_response_text, '$.timeCreated')
      ) RETURNING CLOB PRETTY
    ) INTO l_cleaned_json FROM dual;

    l_pos := 1;
    LOOP
      l_line := SUBSTR(l_cleaned_json, l_pos, 4000);
      EXIT WHEN l_line IS NULL;
      DBMS_OUTPUT.put_line(l_line);
      l_pos := l_pos + 4000;
    END LOOP;
END;
/

I have redacted some data. Here is the output:

If you are only interested to check if it’s a DBCS or not, you can do that:

DECLARE
  l_url          VARCHAR2(4000) := 'http://169.254.169.254/opc/v1/instance/metadata';
  l_http_request UTL_HTTP.req;
  l_http_response UTL_HTTP.resp;
  l_response_text CLOB;
  l_buffer        VARCHAR2(32767);
  l_db_service_type VARCHAR2(100);
BEGIN
  l_http_request := UTL_HTTP.begin_request(l_url, 'GET');
  
  UTL_HTTP.set_header(l_http_request, 'Authorization', 'Bearer Oracle');

  l_http_response := UTL_HTTP.get_response(l_http_request);
  
  LOOP
    UTL_HTTP.read_line(l_http_response, l_buffer, TRUE);
    l_response_text := l_response_text || l_buffer;
  END LOOP;

EXCEPTION
  WHEN UTL_HTTP.end_of_body THEN
    UTL_HTTP.end_response(l_http_response);
    
    SELECT JSON_VALUE(l_response_text, '$.dbServiceType')
    INTO l_db_service_type FROM dual;

    DBMS_OUTPUT.put_line('dbServiceType: ' || l_db_service_type);
END;
/

Here is the output:

OK, this was a short one, but hope it helps!

Vinicius