OCIDatabasesintermediate

OCI NoSQL Database Guide

Build low-latency applications with OCI NoSQL Database: tables, DDL, provisioned and on-demand capacity, global tables, TTL, and secondary indexes.

CloudToolStack Team24 min readPublished Mar 14, 2026

Prerequisites

Basic understanding of NoSQL database concepts
OCI account with NoSQL Database permissions

Introduction to OCI NoSQL Database

OCI NoSQL Database Cloud Service is a fully managed, serverless database designed for applications that require single-digit millisecond latency, flexible data models, and seamless scaling. It supports document (JSON), columnar (fixed-schema), and key-value data models in a single service, providing versatility across a wide range of use cases including user profiles, IoT telemetry, session management, product catalogs, and real-time analytics.

OCI NoSQL is built on Oracle's NoSQL Database technology and provides automatic sharding, replication across fault domains for high availability, and optional global tables for multi-region active-active replication. The service supports both on-demand (serverless) and provisioned capacity modes, allowing you to choose between automatic scaling and predictable pricing.

This guide covers table creation and schema design, DDL operations, capacity planning with on-demand and provisioned modes, global tables for multi-region replication, TTL for automatic data expiration, secondary indexes, and production best practices.

Always Free NoSQL Allowance

OCI NoSQL Database includes Always Free allowances: 25 GB storage, 133 million read units, and 133 million write units per month. This is sufficient for development, testing, and lightweight production workloads. The Always Free allowance is shared across all NoSQL tables in your tenancy.

Creating Tables and Schema Design

NoSQL tables are the primary data container. Each table has a primary key (which determines data distribution across shards) and a schema that defines the columns and their data types. Tables can use either a fixed schema (where all columns are defined upfront) or a JSON column for schema-flexible document storage.

Primary key design is the most important decision in NoSQL database modeling. The primary key determines how data is distributed across shards and which queries can be performed efficiently. A well-designed primary key ensures even data distribution and supports your most common access patterns without full table scans.

OCI NoSQL supports composite primary keys with a shard key component and an optional sort key component. The shard key determines which partition stores the row, while the sort key determines the order of rows within a partition. All rows with the same shard key are stored together, enabling efficient range queries on the sort key.

bash

# Create a table with fixed schema using DDL
oci nosql table create \
  --compartment-id $C \
  --name "users" \
  --ddl-statement "CREATE TABLE users (
    user_id STRING,
    email STRING,
    display_name STRING,
    created_at TIMESTAMP(3),
    last_login TIMESTAMP(3),
    profile JSON,
    PRIMARY KEY(user_id)
  )" \
  --table-limits '{"maxReadUnits": 50, "maxWriteUnits": 50, "maxStorageInGBs": 5}' \
  --wait-for-state ACTIVE

# Create a table with composite primary key (shard + sort)
oci nosql table create \
  --compartment-id $C \
  --name "orders" \
  --ddl-statement "CREATE TABLE orders (
    customer_id STRING,
    order_date STRING,
    order_id STRING,
    status STRING,
    total_amount DOUBLE,
    items JSON,
    PRIMARY KEY(SHARD(customer_id), order_date, order_id)
  )" \
  --table-limits '{"maxReadUnits": 100, "maxWriteUnits": 100, "maxStorageInGBs": 25}' \
  --wait-for-state ACTIVE

# Create a table with on-demand capacity (serverless)
oci nosql table create \
  --compartment-id $C \
  --name "events" \
  --ddl-statement "CREATE TABLE events (
    event_id STRING,
    event_type STRING,
    timestamp TIMESTAMP(3),
    source STRING,
    payload JSON,
    PRIMARY KEY(event_id)
  ) USING TTL 30 DAYS" \
  --table-limits '{"capacityMode": "ON_DEMAND", "maxStorageInGBs": 50}' \
  --wait-for-state ACTIVE

# List all tables
oci nosql table list \
  --compartment-id $C \
  --query 'data.items[].{name:name, "lifecycle-state":"lifecycle-state"}' \
  --output table

DDL Operations and Schema Evolution

OCI NoSQL supports schema evolution through DDL (Data Definition Language) statements. You can add columns, drop columns, modify column defaults, create indexes, and alter table limits without downtime. These operations are performed online and do not block reads or writes to the table.

Schema evolution is one of the advantages of NoSQL over traditional relational databases. You can add new columns at any time, and existing rows will simply have null values for the new columns. Dropping columns is also non-disruptive because existing data is preserved until overwritten.

bash

# Add a column to an existing table
oci nosql table update \
  --table-name-or-id "users" \
  --compartment-id $C \
  --ddl-statement "ALTER TABLE users ADD phone_number STRING" \
  --wait-for-state ACTIVE

# Add multiple columns
oci nosql table update \
  --table-name-or-id "users" \
  --compartment-id $C \
  --ddl-statement "ALTER TABLE users ADD (
    account_status STRING DEFAULT 'active',
    login_count INTEGER DEFAULT 0,
    preferences JSON
  )" \
  --wait-for-state ACTIVE

# Drop a column
oci nosql table update \
  --table-name-or-id "users" \
  --compartment-id $C \
  --ddl-statement "ALTER TABLE users DROP phone_number" \
  --wait-for-state ACTIVE

# Create a secondary index
oci nosql table update \
  --table-name-or-id "users" \
  --compartment-id $C \
  --ddl-statement "CREATE INDEX idx_email ON users(email)" \
  --wait-for-state ACTIVE

# Create a composite index
oci nosql table update \
  --table-name-or-id "orders" \
  --compartment-id $C \
  --ddl-statement "CREATE INDEX idx_status_date ON orders(status, order_date)" \
  --wait-for-state ACTIVE

# Create an index on a JSON field
oci nosql table update \
  --table-name-or-id "users" \
  --compartment-id $C \
  --ddl-statement "CREATE INDEX idx_city ON users(profile.address.city AS STRING)" \
  --wait-for-state ACTIVE

# Drop an index
oci nosql table update \
  --table-name-or-id "users" \
  --compartment-id $C \
  --ddl-statement "DROP INDEX idx_email ON users" \
  --wait-for-state ACTIVE

Capacity Modes: Provisioned vs On-Demand

OCI NoSQL offers two capacity modes that determine how throughput is allocated and billed:

Provisioned Capacity: You specify the maximum read units, write units, and storage for the table. Read and write units are measured in KB per second (1 read unit = 1 eventually consistent read of up to 1 KB per second). Provisioned capacity provides predictable pricing and guaranteed throughput, making it ideal for workloads with consistent traffic patterns.

On-Demand Capacity: The service automatically scales throughput based on actual usage. You only specify the maximum storage. On-demand is billed per read and write operation performed, with no upfront capacity commitment. This mode is best for unpredictable or spiky workloads where you cannot forecast throughput requirements.

You can switch between provisioned and on-demand modes at any time without downtime. When switching from on-demand to provisioned, monitor your actual usage first to set appropriate throughput limits.

bash

# Update table limits (provisioned capacity)
oci nosql table update \
  --table-name-or-id "users" \
  --compartment-id $C \
  --table-limits '{"maxReadUnits": 200, "maxWriteUnits": 100, "maxStorageInGBs": 10}' \
  --wait-for-state ACTIVE

# Switch to on-demand capacity
oci nosql table update \
  --table-name-or-id "users" \
  --compartment-id $C \
  --table-limits '{"capacityMode": "ON_DEMAND", "maxStorageInGBs": 10}' \
  --wait-for-state ACTIVE

# Switch back to provisioned capacity
oci nosql table update \
  --table-name-or-id "users" \
  --compartment-id $C \
  --table-limits '{"capacityMode": "PROVISIONED", "maxReadUnits": 100, "maxWriteUnits": 50, "maxStorageInGBs": 10}' \
  --wait-for-state ACTIVE

# Get current table usage and limits
oci nosql table get \
  --table-name-or-id "users" \
  --compartment-id $C \
  --query 'data.{"table-limits":"table-limits"}'

Start with On-Demand for New Tables

For new tables where you do not yet know the traffic pattern, start with on-demand capacity. Monitor the actual read and write unit consumption for 1-2 weeks using OCI Monitoring metrics. Once you understand the baseline traffic, switch to provisioned capacity with limits set at 20-30% above peak usage for cost optimization.

CRUD Operations and Querying

OCI NoSQL provides a SQL-like query language for reading data and a simple put/get/delete API for individual row operations. The query language supports SELECT, WHERE, ORDER BY, GROUP BY, LIMIT, and OFFSET clauses, as well as JSON path expressions for querying nested document fields.

bash

# Insert a row
oci nosql row update \
  --table-name-or-id "users" \
  --compartment-id $C \
  --value '{"user_id": "u001", "email": "alice@example.com", "display_name": "Alice", "created_at": "2026-03-14T10:00:00.000Z", "profile": {"city": "Austin", "tier": "premium"}}'

# Get a row by primary key
oci nosql row get \
  --table-name-or-id "users" \
  --compartment-id $C \
  --key '["user_id:u001"]'

# Query using SQL
oci nosql query execute \
  --compartment-id $C \
  --statement "SELECT * FROM users WHERE display_name = 'Alice'" \
  --query 'data.items'

# Query with JSON field access
oci nosql query execute \
  --compartment-id $C \
  --statement "SELECT user_id, email, u.profile.city AS city FROM users u WHERE u.profile.tier = 'premium'" \
  --query 'data.items'

# Query with aggregation
oci nosql query execute \
  --compartment-id $C \
  --statement "SELECT status, COUNT(*) AS cnt, SUM(total_amount) AS total FROM orders GROUP BY status" \
  --query 'data.items'

# Range query on composite key (all orders for a customer)
oci nosql query execute \
  --compartment-id $C \
  --statement "SELECT * FROM orders WHERE customer_id = 'c001' AND order_date >= '2026-01-01' ORDER BY order_date DESC LIMIT 10" \
  --query 'data.items'

# Delete a row
oci nosql row delete \
  --table-name-or-id "users" \
  --compartment-id $C \
  --key '["user_id:u001"]'

Global Tables for Multi-Region Replication

Global Tables provide active-active multi-region replication for OCI NoSQL tables. When you create a global table, data is automatically replicated across all specified regions with eventual consistency. Any region can accept reads and writes, and changes are propagated to all other regions asynchronously with typical replication latency of a few seconds.

Global tables are essential for applications that serve users in multiple geographic regions and require low-latency data access regardless of location. They also provide disaster recovery capability because each region maintains a full copy of the data.

bash

# Create a table replica in another region (makes it a global table)
oci nosql table create-replica \
  --table-name-or-id "users" \
  --compartment-id $C \
  --region "us-phoenix-1" \
  --max-read-units 50 \
  --max-write-units 50 \
  --wait-for-state ACTIVE

# Add a third region
oci nosql table create-replica \
  --table-name-or-id "users" \
  --compartment-id $C \
  --region "eu-frankfurt-1" \
  --max-read-units 50 \
  --max-write-units 50 \
  --wait-for-state ACTIVE

# List replicas for a global table
oci nosql table get \
  --table-name-or-id "users" \
  --compartment-id $C \
  --query 'data.replicas'

# Remove a replica
oci nosql table delete-replica \
  --table-name-or-id "users" \
  --compartment-id $C \
  --region "eu-frankfurt-1" \
  --wait-for-state ACTIVE

Global Table Conflict Resolution

Global tables use last-writer-wins conflict resolution based on the timestamp of the write operation. If two regions write to the same row simultaneously, the write with the later timestamp prevails. Design your application to minimize concurrent writes to the same rows across regions. For example, use region-specific primary key prefixes or route writes for a given entity to a single primary region.

TTL: Automatic Data Expiration

Time-to-Live (TTL) allows you to set an expiration time on individual rows or define a default TTL at the table level. When a row's TTL expires, it is automatically deleted by the service without consuming write units. TTL is ideal for session data, temporary tokens, event logs, and any data that has a natural expiration.

TTL can be specified in days or hours. Table-level TTL sets a default for all new rows, but individual rows can override the table default with their own TTL values. A TTL of 0 means the row never expires.

bash

# Set table-level default TTL
oci nosql table update \
  --table-name-or-id "events" \
  --compartment-id $C \
  --ddl-statement "ALTER TABLE events USING TTL 7 DAYS" \
  --wait-for-state ACTIVE

# Insert a row with a specific TTL (overrides table default)
oci nosql row update \
  --table-name-or-id "events" \
  --compartment-id $C \
  --value '{"event_id": "e001", "event_type": "login", "timestamp": "2026-03-14T10:00:00.000Z", "source": "web", "payload": {"user_id": "u001"}}' \
  --ttl '{"value": 24, "timeUnit": "HOURS"}'

# Insert a row that never expires (TTL 0 overrides table default)
oci nosql row update \
  --table-name-or-id "events" \
  --compartment-id $C \
  --value '{"event_id": "e002", "event_type": "critical_alert", "timestamp": "2026-03-14T10:00:00.000Z", "source": "system", "payload": {}}' \
  --ttl '{"value": 0, "timeUnit": "DAYS"}'

# Check TTL on a row
oci nosql row get \
  --table-name-or-id "events" \
  --compartment-id $C \
  --key '["event_id:e001"]' \
  --query 'data.{"expiration-time-ms":"expiration-time-ms"}'

Monitoring and Performance

OCI NoSQL emits detailed metrics to OCI Monitoring for capacity utilization, latency, throttling, and storage. These metrics are essential for right-sizing provisioned capacity, detecting performance issues, and optimizing costs.

bash

# Monitor read unit consumption
oci monitoring metric-data summarize-metrics-data \
  --compartment-id $C \
  --namespace "oci_nosql" \
  --query-text 'ReadUnits[5m]{tableName = "users"}.sum()'

# Monitor write unit consumption
oci monitoring metric-data summarize-metrics-data \
  --compartment-id $C \
  --namespace "oci_nosql" \
  --query-text 'WriteUnits[5m]{tableName = "users"}.sum()'

# Monitor throttled requests
oci monitoring metric-data summarize-metrics-data \
  --compartment-id $C \
  --namespace "oci_nosql" \
  --query-text 'ReadThrottleCount[5m]{tableName = "users"}.sum()'

# Monitor storage usage
oci monitoring metric-data summarize-metrics-data \
  --compartment-id $C \
  --namespace "oci_nosql" \
  --query-text 'StorageGB[1h]{tableName = "users"}.max()'

# Create an alarm for throttling
oci monitoring alarm create \
  --compartment-id $C \
  --display-name "nosql-throttled" \
  --metric-compartment-id $C \
  --namespace "oci_nosql" \
  --query-text 'ReadThrottleCount[5m]{tableName = "users"}.sum() + WriteThrottleCount[5m]{tableName = "users"}.sum() > 100' \
  --severity "WARNING" \
  --destinations '["<ops-topic-ocid>"]' \
  --is-enabled true \
  --body "NoSQL table experiencing throttling - consider increasing capacity"

# Delete a table
oci nosql table delete \
  --table-name-or-id "events" \
  --compartment-id $C \
  --force

Production Best Practices

Running OCI NoSQL in production requires attention to data modeling, capacity planning, and operational monitoring:

Primary Key Design: The shard key should distribute data evenly across partitions. Avoid using low-cardinality fields (like status or region) as the shard key, as this creates hot partitions. High-cardinality fields like user IDs, device IDs, or UUIDs provide the best distribution.

Denormalize for Access Patterns: Unlike relational databases, NoSQL databases do not support joins. Design your tables around your access patterns and duplicate data across tables when needed. If you frequently need user data with their orders, consider embedding recent orders in the user document or creating a denormalized table with both user and order data.

Use JSON Columns Wisely: JSON columns provide schema flexibility but are less efficient for queries than fixed-schema columns. Put frequently queried fields in fixed-schema columns and use JSON for nested or variable-structure data. Create indexes on commonly queried JSON paths for performance.

Capacity Management: For provisioned tables, monitor the ratio of consumed to provisioned capacity. If consistently below 50%, reduce provisioned units to save cost. If throttling occurs, increase capacity immediately. Consider on-demand mode for tables with unpredictable traffic.

Data Lifecycle: Use TTL for all temporary data to prevent unbounded table growth. Implement archival workflows that move cold data to Object Storage before it consumes expensive NoSQL storage. Review TTL policies quarterly to ensure they match current business requirements.

Autonomous Database on OCI OCI Object Storage & Tiers OCI Functions Serverless Guide

Key Takeaways

1OCI NoSQL supports document (JSON), columnar, and key-value data models in a single service.
2On-demand and provisioned capacity modes allow switching between pay-per-use and predictable pricing.
3Global tables provide active-active multi-region replication with eventual consistency.
4TTL enables automatic data expiration without consuming write units for deletion.

Frequently Asked Questions

How does OCI NoSQL compare to AWS DynamoDB?

OCI NoSQL and DynamoDB are functionally similar serverless NoSQL databases. Both support on-demand and provisioned capacity, secondary indexes, and TTL. OCI NoSQL includes 25 GB free storage (vs 25 GB for DynamoDB). OCI NoSQL supports SQL-like queries with GROUP BY and aggregations natively, while DynamoDB requires PartiQL. OCI NoSQL also allows switching between capacity modes without restrictions.

What is the maximum table size in OCI NoSQL?

OCI NoSQL tables can grow to virtually unlimited size. Individual rows can be up to 512 KB. The service automatically shards data across partitions based on the primary key. Storage is billed per GB-month with no pre-provisioning required. Performance scales linearly with provisioned capacity or automatically with on-demand mode.

Written by CloudToolStack Team

Cloud engineers and architects with hands-on experience across AWS, Azure, and GCP. We write guides based on real-world production patterns, not just documentation rewrites.

Disclaimer: This guide is for educational purposes. Cloud services change frequently; always refer to official documentation for the latest information. AWS, Azure, and GCP are trademarks of their respective owners.