Oracle Cloud (OCI)

Oracle Cloud Infrastructure (OCI) is Oracle's public cloud platform — the collection of compute, storage, networking, and database services that Oracle runs in its own data centres and offers to customers on a pay-as-you-go basis. Instead of buying and maintaining physical servers, customers provision resources in minutes and pay only for what they use. For Oracle Database specifically, OCI offers options ranging from a fully managed, self-driving database with no DBA work required, to a cloud-hosted virtual machine where you manage Oracle yourself exactly as you would on-premises.

OCI vs On-Premises Oracle

The SQL you write does not change when you move to OCI — Oracle Database is Oracle Database regardless of where it runs. What changes is how the infrastructure is managed. On-premises you own the hardware, install the OS, patch Oracle, manage storage, configure backups, and plan capacity. In OCI, Oracle takes over some or all of those responsibilities depending on which service you choose. The trade-off is control vs convenience.

• On-premises: full control — you manage everything from hardware to application. Maximum flexibility, maximum responsibility.
• OCI VM DB System: Oracle manages the hardware and provides a pre-built OS image — you manage the Oracle database installation, patching, and backups yourself
• OCI Base Database Service: Oracle manages more — hardware, OS, and Oracle software installation — you control database configuration, schemas, and backups
• Autonomous Database: Oracle manages everything — hardware, OS, database software, patching, tuning, backups, and scaling — you write SQL and manage schemas
• Data transfer costs and latency are the main considerations when deciding whether to run workloads in OCI vs on-premises

Core OCI Database Services

OCI offers several database deployment options. Choosing the right one depends on how much control you need, how much management work you want to do, and what your licensing situation is.

-- Once connected to any OCI Oracle Database, standard SQL works identically
-- Check which version and edition is running
SELECT banner FROM v$version;
-- Check the current container (relevant in Autonomous Database which uses CDB/PDB architecture)
SELECT sys_context('USERENV', 'CON_NAME') AS container_name,
       sys_context('USERENV', 'DB_NAME')  AS db_name
FROM   dual;
-- Autonomous Database: check if auto-indexing is enabled
SELECT status FROM dba_auto_index_config WHERE parameter_name = 'AUTO_INDEX_MODE';
-- Check Automatic Storage Management (ASM) disk group usage in cloud DB systems
SELECT name, total_mb, free_mb,
       ROUND((total_mb - free_mb) * 100 / total_mb, 1) AS pct_used
FROM   v$asm_diskgroup;

• All Oracle SQL, PL/SQL, and data dictionary views work identically across on-premises, OCI DB Systems, and Autonomous Database — portability is a core Oracle design principle
• Autonomous Database runs as a Pluggable Database (PDB) inside a Container Database (CDB) — you connect to your PDB and never see the CDB layer
• AUTO_INDEX_MODE = IMPLEMENT means Autonomous Database will automatically create, test, and drop indexes based on workload analysis — this can be set to REPORT ONLY if you want recommendations without automatic application
• ASM (Automatic Storage Management) manages disk groups in OCI DB Systems — you interact with it for storage diagnostics but OCI handles the underlying provisioning

Autonomous Database

Autonomous Database (ADB) is Oracle's flagship cloud offering — a fully self-managing database that eliminates most DBA tasks. It comes in two shapes: Autonomous Data Warehouse (ADW) optimised for analytics and reporting workloads, and Autonomous Transaction Processing (ATP) optimised for mixed OLTP and analytics. ADB patches itself, scales CPU and storage independently on demand, tunes itself using machine learning, and backs itself up automatically every day with 60-day retention. You provision it through the OCI console, a REST API, or Terraform, and connect with a standard Oracle client using a wallet download.

• ADB can scale OCPUs (Oracle CPU units) up and down in seconds — useful for known batch windows or unpredictable traffic spikes
• Always Free tier: Oracle provides two Always Free ADB instances (one ADW, one ATP) with 1 OCPU and 20 GB storage each — suitable for learning and development
• Connecting to ADB requires downloading a wallet from the OCI console — a zip file containing connection strings, certificates, and credentials
• ADB blocks some operations that are permitted on on-premises databases — you cannot create tablespaces, modify SGA parameters, or use Data Pump to the local filesystem directly; OCI Object Storage is used instead
• The Database Actions web interface (formerly SQL Developer Web) is available directly from the OCI console — no local client installation needed

Connecting to OCI from SQL Developer

Connecting SQL Developer to an OCI database uses the same interface as an on-premises connection, with one addition for Autonomous Database: the wallet zip file must be downloaded from the OCI console and pointed to in the connection settings. For OCI DB Systems (non-Autonomous), a standard TCP connection with the database hostname and port is sufficient, provided the VCN security rules allow port 1521.

-- Connection string format for Autonomous Database (from tnsnames.ora inside the wallet)
-- myadb_high = (DESCRIPTION =
--     (ADDRESS = (PROTOCOL = TCPS)(HOST = adb.us-ashburn-1.oraclecloud.com)(PORT = 1522))
--     (CONNECT_DATA = (SERVICE_NAME = abc123xyz_myadb_high.adb.oraclecloud.com))
--     (SECURITY = (SSL_SERVER_DN_MATCH = YES)))
-- Service name suffixes on Autonomous Database:
-- _high    -- highest priority, most resources, maximum parallelism (use for heavy queries)
-- _medium  -- balanced priority (use for general workloads)
-- _low     -- lowest priority, minimal resources (use for background jobs)
-- _tp      -- transaction processing optimised (ATP only)
-- _tpurgent -- urgent transaction processing with highest priority (ATP only)
-- After connecting, standard SQL works exactly as on-premises
SELECT table_name FROM user_tables ORDER BY table_name;
-- Check current connection service level
SELECT sys_context('USERENV', 'SERVICE_NAME') AS service FROM dual;

• The _high service uses the most resources and highest parallelism — use it for long-running analytical queries
• The _low service is rate-limited — good for non-urgent batch jobs and background tasks that should not compete with interactive workloads
• Autonomous Database uses mTLS (mutual TLS) by default — both the client and server authenticate each other using certificates in the wallet
• OCI DB Systems (non-Autonomous) can also be accessed over TLS without a wallet if the network security group allows port 1522 and the database is configured for one-way TLS

Moving Data To and From OCI

Getting data into and out of OCI databases is a common operational task. For Autonomous Database, Data Pump integrates with OCI Object Storage — a scalable cloud storage service — rather than the local filesystem. You upload dump files to Object Storage and import from there, or export directly to a bucket. For non-Autonomous DB Systems, standard Data Pump with a directory object pointing to a network file system or cloud storage mount works normally.

-- On Autonomous Database: create a credential for OCI Object Storage access
-- Required before Data Pump can read or write to Object Storage buckets
BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'OBJ_STORE_CRED',
        username        => 'user@example.com',       -- OCI account email
        password        => 'MyAuthToken123#'          -- OCI auth token (not account password)
    );
END;
/
-- Import a Data Pump dump file stored in Object Storage into Autonomous Database
BEGIN
    DBMS_DATAPUMP_CLOUD.IMPORT(
        credential_name => 'OBJ_STORE_CRED',
        file_uri_list   => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/mytenancy/b/mybucket/o/store_schema.dmp',
        schema_name     => 'DATAPLEXA_STORE'
    );
END;
/
-- Load a CSV file directly from Object Storage into a table (no Data Pump needed)
BEGIN
    DBMS_CLOUD.COPY_DATA(
        table_name      => 'ORDERS',
        credential_name => 'OBJ_STORE_CRED',
        file_uri_list   => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/mytenancy/b/mybucket/o/orders.csv',
        format          => JSON_OBJECT('type' VALUE 'csv', 'skipheaders' VALUE '1')
    );
END;
/

• DBMS_CLOUD is an Autonomous Database-specific package — it is not available on on-premises Oracle or OCI DB Systems without additional installation
• OCI auth tokens are generated in the OCI console under Identity → My Profile — they are separate from your account password and can be revoked individually
• DBMS_CLOUD.COPY_DATA is much simpler than Data Pump for loading flat files — it reads CSV, JSON, Parquet, and other formats directly from Object Storage
• All data transfer between Autonomous Database and Object Storage within the same OCI region is free — cross-region transfers incur egress charges

Lesson Summary

Practice Questions

Quiz

Next up — Mini Project — Apply everything you have learned across the course to design a schema, write queries, build PL/SQL logic, and optimise a realistic Oracle database scenario from scratch.