Scenario: Connect to IoT Data with Database Tools

Create a private, token-authenticated Oracle Database Tools connection to the IoT domain database.

After you configure direct database connectivity for an IoT domain, create a reusable Oracle Database Tools connection to the IoT domain database. The connection uses the IoT database connection string, a private endpoint in the allowlisted subnet, IAM token authentication, and a proxy client user for the IoT domain workspace.

A Database Tools connection is a reusable connection definition. You can validate the connection in Database Tools and then use it with supported Database Tools clients and OCI services, such as SQL Worksheet, a Database Tools managed MCP server, OCI AI Agent integrations where supported, or other integrations that need to connect to the database.

Tasks

Before You Begin
Step 1: Configure Direct Database Connectivity for IoT
Step 2: Create a Private Endpoint for Database Tools
Step 3: Create a Database Tools Connection
Step 4: Validate the Database Tools Connection
Step 5: Use the Connection with SQL Worksheet
FAQ
Troubleshooting
What's Next

Before You Begin

Confirm that you have:

Permissions to configure IoT domain group data access, IoT domain direct data access, networking resources, IAM dynamic groups, bastion resources, and Database Tools resources.
An IoT domain group and IoT domain in the same region as the networking resources.
The IoT domain group OCID, IoT domain OCID, database connection string, database token scope, and IoT domain short ID. Find these values on the domain group and domain details pages, see Getting an IoT Domain Group's Details and Getting an IoT Domain's Details.
A subnet that is allowed to reach the Autonomous AI Database data host.
A policy that lets a user group from the Identity Domain access the IoT domain for direct data access and provides access to the database tools family:
```
allow group <identity-domain-name>/<group-name> to use database-tools-family in compartment <dbtools-compartment>
```

Note

The proxy client user name uses the IoT domain short ID in this format: <domainShortId>__WKSP.

Step 1: Configure Direct Database Connectivity for IoT

Configure direct access to the IoT database before you create the Database Tools connection. This setup allows approved resources in your private network to connect to the IoT domain database.

Here's an overview of the process or for the end-to-end setup, including VCN, compute, bastion, identity dynamic group, and SQLcl examples, see Scenario: Connecting Directly to the IoT Database.

Note

Database Tools uses the private endpoint that you create in this scenario. The compute and bastion steps in the direct-connect scenario are used when you need a host for CLI setup, token retrieval, or validation. If you use the bastion path, the bastion SSH port forwarding command only starts the tunnel and keeps running in that terminal. Open a separate terminal to SSH to the compute instance through the forwarded local port before installing CLI tools or running validation commands.

Open the navigation menu, select Networking, Virtual Cloud Networks, and then select Create VCN. Create the VCN in the same region as the IoT domain group and IoT domain.
Create a private regional subnet in the VCN. Enable DNS host names for the subnet.
Create a service gateway for the VCN and add a route rule that sends Oracle Services Network traffic to the service gateway.
Create or update a network security group so resources in the subnet can send egress traffic required for the database connection.
Add the VCN OCID to the IoT domain group allowlist. Use the oci iot domain-group configure-data-access command or the equivalent Console or API flow.
Get the IoT domain group details and record the db-connection-string and db-token-scope values.
Allowlist the identity group or dynamic group on the IoT domain for direct database access. Use the oci iot domain configure-direct-data-access command or the equivalent Console or API flow.

Examples:

oci iot domain-group configure-data-access \
  --iot-domain-group-id <iot-domain-group-OCID> \
  --db-allow-listed-vcn-ids '["<vcn-OCID>"]'

oci iot domain-group get \
  --iot-domain-group-id <iot-domain-group-OCID>

oci iot domain configure-direct-data-access \
  --iot-domain-id <iot-domain-OCID> \
  --db-allow-listed-identity-group-names '["<tenancy-OCID>:<identity-domain-name>/<identity-group-name>"]'

oci iam db-token get \
  --auth instance_principal \
  --scope urn:oracle:db::id::<database-compartment-OCID>

Step 2: Create a Private Endpoint for Database Tools

Create a Database Tools private endpoint in the same subnet that you configured for direct IoT database connectivity. Database Tools uses this private endpoint to reach the Autonomous AI Database without a public database path.

Open the navigation menu, select Developer Services, select Database Tools.
Select Create private endpoint.
Select the Compartment, VCN, and Subnet that are allowlisted for direct IoT database connectivity.
Select the Network security group required for the private endpoint, if your network design uses one.
Select Create and wait until the private endpoint becomes active.

Note

The private endpoint must be able to reach the data host in the database connection string. If you created more than one subnet for the direct to database connection workflow, use the subnet that has the route and security rules required for database egress.

Step 3: Create a Database Tools Connection

Create a Database Tools connection that uses the IoT database connection string, the private endpoint, and IAM token authentication.

In Database Tools, select Create connection.
Choose Enter database information.
For Connection type, select Oracle AI Database.
In Connection string, enter the database connection string from the IoT domain group details.
Choose Access database via a private network, and then select the private endpoint that you created in Step 2.
Set Wallet format to None.
Scroll to Advanced options, and then select Authentication.
Enable Use token based authentication.
Select Add connection property:
- Name: iam.db.token.scope
- Value: The IoT database token scope, for example urn:oracle:db::id::<database-compartment-OCID>.
Set Proxy authentication type to Single Session.
Set Proxy client user name to <domainShortId>__WKSP.
Select Create.

Note

The Username field can appear as required when you first select Enter database information. After you enable token-based authentication in Advanced options, the required username field is no longer needed.

Connection string pattern:

tcps://adb.<region>.oraclecloud.com:1521/<database-service-name>.adb.<region>.oraclecloud.com

Example:

tcps://adb.us-phoenix-1.oraclecloud.com:1521/g896eaa614b05d8_cx3garwvwsieq_low.adb.oraclecloud.com

Step 4: Validate the Database Tools Connection

Validate the connection before you use it from SQL Worksheet or another supported Database Tools integration.

In Database Tools, open the connection that you created in Step 3.
Open the connection's Actions menu.
Select Validate.
Review the validation result.
If validation fails, edit the connection, private endpoint, network, token scope, authentication, or proxy user settings, and then validate again.

A successful validation confirms that Database Tools can use the private endpoint and token-based authentication settings to reach the IoT domain database.

Step 5: Use the Connection with SQL Worksheet

SQL Worksheet is one way to use the Database Tools connection. Use it when you want to run ad hoc SQL queries against IoT data from the Console.

In Database Tools, select SQL worksheet.
Select the Connection that you validated in Step 4.
Run a simple query to confirm the session and schema context.
List the available IoT database objects.
Run the queries required for your IoT investigation or reporting workflow.
On the left, select the schema and open views and tables to see your IoT data.

Examples:

select sys_context('USERENV', 'CURRENT_USER') as current_user,
       sys_context('USERENV', 'CURRENT_SCHEMA') as current_schema
from dual;

select table_name
from user_tables
order by table_name;

select *
from <iot-schema-or-view-name>
fetch first 10 rows only;

Start with discovery queries when you are not sure which IoT schema object contains the data you need. Then narrow the query by device, digital twin instance, metric, event type, or time range.

FAQ

Why do I need the direct IoT database connectivity setup before creating the Database Tools connection?: Database Tools still needs a network path, token scope, and database connection string for the IoT domain database. The direct-connect workflow configures the VCN allowlist, identity allowlist, database token scope, and database connection string that the Database Tools connection uses.
Why should the private endpoint use the same subnet?: The private endpoint must use a subnet that can reach the Autonomous AI Database data host and is part of the direct-connect network design. Using the same allowlisted subnet keeps Database Tools on the same private network path that you validated for IoT database access.
What value should I use for iam.db.token.scope?: Use the db-token-scope value from the IoT domain group details. The value starts with urn:oracle:db::id:: and ends with the database compartment OCID, for example urn:oracle:db::id::<database-compartment-OCID>.
Why does the username field appear when I create the connection?: The Username field can appear before you configure authentication. Open Advanced options, select Authentication, and enable Use token based authentication. After token-based authentication is enabled, the username field is no longer required.
What proxy client user name should I enter?: Use the IoT domain short ID followed by __WKSP, for example <domainShortId>__WKSP. This proxy client user connects the Database Tools session to the IoT domain workspace user.
Why is wallet format set to none?: The connection uses the walletless TCPS connection string and IAM token-based authentication. Set Wallet format to None so the connection does not require an uploaded wallet file.
Do I have to use SQL Worksheet with this connection?: No. SQL Worksheet is one client that can use the Database Tools connection. The connection is a reusable connection definition that can also be selected by other supported Database Tools clients and OCI services, such as a Database Tools managed MCP server, OCI AI Agent integrations where supported, or other integrations that need a database connection.

Troubleshooting

The connection form still requires a username

Scroll to Advanced options, open Authentication, and enable token-based authentication. The username requirement is removed after token authentication is selected.

Database Tools cannot connect through the private endpoint

Confirm that the private endpoint uses the same VCN and subnet that are allowlisted for direct IoT database connectivity. Check the subnet route table, service gateway route, network security group, and egress rules.

The token scope is rejected

Use the db-token-scope value from the IoT domain group details. The value begins with urn:oracle:db::id::. Do not enter a display name or a placeholder such as db_database.

The proxy session does not show the expected IoT objects

Check the proxy client user name. It must use the IoT domain short ID followed by __WKSP, for example <domainShortId>__WKSP. Confirm that direct data access is configured for the IoT domain and that the identity group or dynamic group is allowlisted.

What's Next

Review the IoT domain database schema and identify the tables or views required for your workflow.
Use the Database Tools connection from SQL Worksheet, a Database Tools managed MCP server, OCI AI Agent integrations where supported, or another supported OCI service integration.
IoT MCP server
Use Select AI or APEX when you want natural-language query generation or a browser-based SQL workflow over the same IoT data.
Java samples for accessing OCI IoT domain data using direct database queries to count raw records or by subscribing to queues to stream raw and normalized messages in real time.

Oracle Cloud Infrastructure Documentation