
A developer joins a bank, opens the main database for the first time, and runs the query that has worked everywhere else in their career:
SELECT * FROM customers WHERE middle_name = '';
Zero rows. Not because every customer has a middle name, but because in Oracle an empty string is NULL, and nothing ever equals NULL.
Oracle Database is full of moments like this. It is one of the oldest and most capable databases there is, it holds the core records of many banks, airlines, telecom companies and governments, and it does a number of things in its own way. This guide is a map for newcomers: what Oracle is, when it is worth its price, and the rules you need on day one.
What Oracle Database is
Oracle Database is a commercial relational database, first sold in the late 1970s and developed continuously since. You query it with SQL and write stored logic in PL/SQL, its procedural language.
It is proprietary and licensed, usually per processor or per named user, with many advanced features sold as separately priced options. That price is the first thing people mention about Oracle. The second is that it runs enormous, critical workloads very reliably.
You do not have to pay to learn it. Oracle Database Free is a no-cost edition with limits on CPU, memory and data size, and it runs in a container:
docker run -d --name oracle-free -p 1521:1521 \
-e ORACLE_PWD=Use-A-Strong-One-1 \
container-registry.oracle.com/database/free:latest
Words you need first
Oracle uses familiar words in unfamiliar ways:
| Term | What it means in Oracle |
|---|---|
| Instance | The running database software: its memory and processes |
| Container database (CDB) | The outer database that holds one or more pluggable databases |
| Pluggable database (PDB) | A self-contained database inside the container. Your application connects to one of these |
| Service name | The name you connect to, which leads to a PDB |
| Schema | A user. Each user owns one schema with the same name, and tables live in it |
| Tablespace | A named area of storage where tables and indexes are kept |
So "create a schema" in Oracle means "create a user". And a connection needs a host, a port (1521 by default) and a service name:
sqlplus shop_app@//localhost:1521/FREEPDB1
FREEPDB1 is the pluggable database that Oracle Database Free creates for you.
Why organisations choose it
Consistent reads without blocking
Oracle was an early user of multi-version read consistency. A query sees the data as it was when the query started, however long it runs, and it never blocks people who are writing. Writers do not block readers either.
High availability at scale
Real Application Clusters (RAC) lets several servers run one database, so a server can fail without an outage. Data Guard keeps a standby database in step with the primary for disaster recovery. These are mature, widely used and well understood, though they are paid options.
Getting data back
Flashback features let you look into the past. You can query a table as it was a few minutes ago, as long as the database still holds the old versions:
SELECT id, status, total
FROM orders AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE)
WHERE id = 42;
When someone runs a bad UPDATE, this turns a restore-from-backup afternoon into a five-minute fix.
Depth
Partitioning of very large tables, fine-grained security, auditing, powerful PL/SQL and decades of tuning tools: if a large enterprise needs a database feature, Oracle probably has it.
Where Oracle fits, and where it does not
A strong choice for:
- Core systems at large organisations: banking, billing, reservations, ERP
- Packaged enterprise software that is certified on Oracle
- Very large databases that need clustering and a hot standby
- Companies that already have Oracle licences and skilled administrators
Look elsewhere when:
- You are a startup or a small team. PostgreSQL covers most of the same ground with no licence cost. Oracle's own free edition is for learning and small workloads.
- You want to avoid lock-in. PL/SQL and Oracle-specific SQL are expensive to migrate away from.
- You have no database administrator. Oracle rewards expertise and can punish its absence, in both performance and licensing.
- The job is simple. An embedded store, a cache or a small website does not need this much machinery.
Using it the right way
Learn the dialect's rules
CREATE TABLE orders (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id NUMBER NOT NULL REFERENCES customers (id),
total NUMBER(12,2) NOT NULL CHECK (total >= 0),
status VARCHAR2(20) DEFAULT 'pending' NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);
-- The newest 20 rows
SELECT id, total FROM orders
ORDER BY created_at DESC
FETCH FIRST 20 ROWS ONLY;
-- A calculation with no table uses the one-row table DUAL
SELECT SYSDATE FROM dual;
- Use
VARCHAR2for text andNUMBERfor numbers. - An empty string is
NULL. Test withIS NULL, never= ''. DATEholds a date and a time to the second. Forgetting that is a common source of off-by-a-day bugs.- Names you do not put in double quotes are stored in upper case.
ordersandORDERSare the same table."orders"in quotes is a different one, so avoid quoting names. - Identity columns and
FETCH FIRSTarrived in version 12c. Older code uses sequences with triggers andROWNUMfor the same jobs.
Commit on purpose
Oracle does not auto-commit your changes in tools such as SQL*Plus. An UPDATE is visible only to your session until you run COMMIT. Forget, and other sessions wait on your locks; close the window carelessly, and your work may be rolled back.
The opposite surprise: DDL statements such as CREATE TABLE and ALTER TABLE commit immediately, and they also commit any change you had pending.
Always use bind variables
Oracle caches the plan for each distinct SQL text. If your application builds a new statement for every value, the cache fills with near-duplicates and the server spends its time parsing.
-- Wrong: a different statement for every customer
SELECT total FROM orders WHERE customer_id = 42;
-- Right: one statement, reused
SELECT total FROM orders WHERE customer_id = :customer_id;
Bind variables also close the door on SQL injection. Every Oracle driver supports them.
Read the plan
EXPLAIN PLAN FOR
SELECT id, total FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
TABLE ACCESS FULL on a big table means every row is read. INDEX RANGE SCAN means an index is doing the work. Oracle does not index foreign key columns automatically, so add those indexes yourself:
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at);
The optimiser depends on statistics. If a plan looks wrong after a large data load, refresh them:
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');
Give each application its own user
CREATE USER shop_app IDENTIFIED BY "Use-A-Long-Random-One-1"
QUOTA 500M ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE TO shop_app;
Never let an application connect as SYS or SYSTEM. For reporting, create a separate user and grant it SELECT on only the tables it needs.
Export with Data Pump, back up with RMAN
Data Pump moves schemas between databases:
expdp shop_app@//localhost:1521/FREEPDB1 schemas=SHOP_APP \
directory=DATA_PUMP_DIR dumpfile=shop.dmp logfile=shop_export.log
The file is written on the database server, in the folder that the DATA_PUMP_DIR directory object points to, not on your own machine. For real backups with point-in-time recovery, Oracle's tool is RMAN (Recovery Manager). An export is a useful copy, not a backup strategy.
Common mistakes
- Comparing with an empty string.
= ''never matches. This also means aNOT NULLcolumn rejects empty text. - Building SQL by gluing values together. It hurts performance on Oracle more than on most databases, and it is a security hole.
- Using features you have not licensed. Some diagnostic and tuning views belong to paid packs, and partitioning is an extra-cost option. Running them can create a bill at the next audit. Ask your administrator what you are licensed for.
- Uncommitted work left in a session. It holds row locks and blocks colleagues.
- Long reports against busy tables with too little undo. The result is the well-known
ORA-01555: snapshot too old. Run the report at a quieter time or have the undo retention raised. - Mixing schemas and users in your head. Granting a privilege to a user affects what they can do; where an object lives depends on which schema owns it.
- Quoted lower-case names. A table created as
"orders"must be quoted forever after.
Quick reference
| Task | Command |
|---|---|
| Connect | sqlplus user@//host:1521/service_name |
| Your tables | SELECT table_name FROM user_tables; |
| Describe a table | DESC orders |
| Current user | SELECT USER FROM dual; |
| Version | SELECT banner FROM v$version; |
| Sessions | SELECT sid, serial#, username, status FROM v$session WHERE type = 'USER'; |
| End a session | ALTER SYSTEM KILL SESSION 'sid,serial#'; |
| Query plan | EXPLAIN PLAN FOR ... then SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); |
| Refresh statistics | EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS'); |
| Row as it was earlier | SELECT ... AS OF TIMESTAMP ... |
Oracle is not hard so much as particular. Learn its vocabulary, commit deliberately, bind your variables and respect the licence, and it will do what it has done for decades: keep very important data safe.
Exploring Oracle with CreamDB
Oracle's own tools are powerful and heavy. For a quick look at a schema from any machine, CreamDB connects to Oracle from a browser tab. There is nothing to install, including no Oracle client software.
- Connect with a host, port and service name. Schemas appear in a tree, yours first.
- Open a table in a grid with paging, sorting and filtering done by the database, and export results to CSV, JSON or Excel.
- Write SQL in an editor with autocomplete, formatting and history.
- Read users and roles on a read-only page.
- Ask in plain English and get the SQL, a result table and a chart.
The assistant only reads on its own. Any change waits as an approval card that you answer, and read-only mode is enforced on the server. A database on a private network can be reached through an SSH tunnel.
The live Status panel is not available for Oracle today; it covers PostgreSQL, MySQL, MariaDB, SQL Server and ClickHouse.
It is free to start.
About the author
- CreamDB is a web-based database explorer with an AI assistant. Connect your databases, ask questions in plain English and get answers, tables and charts from any browser. Product news and tips here.
Comments
Join the discussionNo comments yet. Be the first to comment.




