Skip to content
Contact →

Working with Oracle Database

Updated
2 Oct 2026
4 min read
6 sections
Page 20 of 29
Data Migration Masterclass

Section 4: Database Platforms for Migration. Lesson 20.

Oracle Database is a powerful, widely used enterprise RDBMS developed by Oracle Corporation. It is common in large organisations in banking, healthcare, telecommunications, government, manufacturing and ERP (for example Oracle E-Business Suite and many SAP installations). It is known for scalability, reliability, security, high availability and advanced management features.

Oracle now markets its latest release as Oracle AI Database, with AI capabilities built into the database itself rather than bolted on as a separate system.

Oracle in migration projects

Oracle shows up on both sides:

  • As a source: very common. Organisations migrate off Oracle to PostgreSQL, SQL Server, MySQL or cloud-native databases, often to cut licence and support costs or to escape vendor lock-in.
  • As a target: companies move to Oracle for its enterprise-grade capabilities and support for mission-critical applications, or to consolidate onto Oracle applications.

Because so many large enterprise migrations involve Oracle, Oracle skills are particularly valuable for migration professionals.

What Oracle offers

  • Converged database: relational, JSON, spatial, graph, vector and more in one engine. This reduces complexity, risk and cost by keeping everyday workloads in one environment, with security suited to stock-exchange-scale systems.
  • AI built in: vector search and AI features run inside the database, so there is no need for separate AI-specialised databases, pipelines or infrastructure. That means less data sprawl, simpler operations, lower total cost of ownership and less maintenance.
  • MCP support: Model Context Protocol lets AI agents powered by LLMs query organisational data in the database to answer questions accurately, within database security rules.
  • Built-in data privacy protection, a unified memory core, Exadata engineered systems for AI, and NVIDIA-accelerated database processing.

Oracle's own framing: "end data chaos, end data risk, end data lock-in." Treat that as marketing. Moving off Oracle is itself one of the harder migrations in the industry.

Editions and cost

  • Oracle Database Free: a free edition for developers and learning, with resource limits. It replaced Express Edition (XE).
  • Standard Edition 2 and Enterprise Edition: paid licences, with Enterprise options such as partitioning, RAC and Advanced Security licensed separately.
  • Cloud: Autonomous Database on Oracle Cloud Infrastructure (with an Always Free tier), plus Oracle Database@Azure, @AWS and @Google Cloud.

So Oracle is not only a trial: you can practise free with Oracle Database Free or an Always Free autonomous database, but production use is typically licensed and expensive.

Oracle migration lifecycle

text
Object inventory → Schema analysis → Data profiling → Compatibility assessment
      → Data mapping → Extract → Transform → Load → Validation → Go-live
  1. Object inventory: list every table, view, package, procedure, function, trigger, sequence, synonym and job.
  2. Schema analysis: in Oracle a schema is the set of objects owned by one user; analyse each one's structure and dependencies (covered in lesson 21).
  3. Data profiling: as in lesson 12.
  4. Compatibility assessment: check whether types, formats and features have standard equivalents in the target.
  5. Data mapping: source to target columns and types, which is often the most complex step.
  6. Extract, transform, load into the target.
  7. Validation: row counts, checksums and business reconciliation, with particular rigour on Oracle numeric and date types.
  8. Go-live: cutover to the new system.

Why Oracle migrations are harder

  • PL/SQL packages: large bodies of procedural code grouped into packages, which must be rewritten for other engines.
  • Oracle-specific types: NUMBER without precision, VARCHAR2, DATE that includes a time, and empty strings treated as NULL.
  • Sequences, synonyms, database links and materialised views, which have no direct equivalent everywhere.
  • Proprietary SQL: CONNECT BY hierarchies, DUAL, ROWNUM, NVL and DECODE.
  • Architecture: an Oracle instance (memory and background processes) serves one or more databases (data files, control files and redo logs), with pluggable databases inside a container database.
sql
-- Inventory the objects in a schema
SELECT object_type, COUNT(*) AS objects
FROM all_objects
WHERE owner = 'SALES'
GROUP BY object_type
ORDER BY objects DESC;

-- Lines of PL/SQL to rewrite: a quick effort estimate
SELECT type, COUNT(*) AS lines
FROM all_source
WHERE owner = 'SALES'
GROUP BY type;

Tools: Oracle SQL Developer (including its migration features), Data Pump (expdp and impdp) for exports, GoldenGate for real-time replication, Zero Downtime Migration for moving into Oracle Cloud; and, for leaving Oracle, Ora2Pg, AWS SCT and DMS, and SSMA for Oracle.

Key takeaways

  • Oracle is the leading enterprise RDBMS in banking, healthcare, telecoms, government and ERP.
  • It is frequently both a migration source and a migration target.
  • The lifecycle runs from inventory and schema analysis through mapping, ETL and validation to go-live.
  • PL/SQL, proprietary types and SQL, and Oracle's architecture make these migrations harder.
  • Oracle AI Database adds in-database AI, vector search and MCP access for AI agents.

Next: 21. Schema Design and Comparison.