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