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