Skip to main content
AIDevOps
  • Learn
  • Learning Paths
  • Practice
  • Open Source
  • Books
  • Engineering

    AI DevOpsThe full map of building and operating AI servicesLLMOpsLLM deployment · evaluation · observabilityHands-on ProjectsBuild AI Agent projects

    Knowledge

    DocsTechnical documentationBlogEngineering articlesPloggerDevelopment log feed

    Validate

    Certification3-level skills certification · coming soon
AI Models
LlamaTutorial|MistralTutorial|GemmaTutorial|DeepSeekTutorial|QwenTutorial
💾 Database
DB Intro & RoadmapCore SQLOracleMySQLPostgreSQL|MongoDBVector DB
🤖 AI Applied Development
Applied AI Intro & RoadmapHugging FaceLangChainLlamaIndexLLMOps|LangGraphMCPMulti-AgentAgent Evaluation
🧠 AI Core
AI Intro & RoadmapML FundamentalsLLM Fundamentals|Python AIC++|PyTorchTensorFlowJAX
🧠 AI Agent Development
Finance AI AgentLLM API ServerStock Investing AgentAIOps AI AgentEducation AI AgentCoding AI Agent
🌱 Spring Cloud
Spring Intro & RoadmapSpring Cloud GatewaySpring BootJava|Spring AISpring SecuritySpring BatchSpring JPA
🐳 DevOps
DevOps Intro & RoadmapLinuxDockerCI/CD|Kubernetes BasicsK8s AdvancedPrometheusGrafana
🧱 Infrastructure
Infrastructure Intro & RoadmapNginxRedis
☁️ Cloud
Cloud Intro & RoadmapAWSGCPAzureNCPCloudflare
🎨 Frontend
Frontend Intro & RoadmapJavaScriptTypeScript|ReactNext.js|VueNuxt|Electron
📱 Mobile
Mobile Intro & RoadmapKotlinAndroidFlutter
⚙️ Backend
Backend Intro & RoadmapPython BasicsFastAPIDjangoFlask|CGoGinNode.js
🧪 Testing
k6JMeternGrinder
AIDevOps

Engineering AI. From Code to Production.
An engineering learning platform for building and operating AI and AI Agents

Learn

  • All Guides
  • Learning Paths
  • AI Tutorial
  • Practice
  • Books

Resources

  • AI DevOps
  • LLMOps
  • Hands-on Projects
  • Docs
  • Blog
  • Plogger
  • Open Source
  • Certification (coming soon)

Start Here

  • AI Core Roadmap
  • AI Applied Development Roadmap
  • Spring Cloud Roadmap
  • DevOps Roadmap
  • Infrastructure Roadmap

 

  • Cloud Roadmap
  • Frontend Roadmap
  • Mobile Roadmap
  • Backend Roadmap
  • Database Roadmap
© 2026 AIDevOps. All rights reserved.
Terms of ServicePrivacy PolicySitemaptestforge.kr
  1. Home
  2. Learn
  3. Database
  4. Oracle
Practical guide to the enterprise Oracle database

🔴 Oracle Complete Guide

Visitors

Learn the structure and key practical uses of Oracle Database, widely used in large enterprise environments such as finance and manufacturing. Covers tablespace management, writing PL/SQL, hint-based execution plan tuning, and the distinctive architecture of Oracle.

  • Advanced
  • Updated Oct 10, 2026
  • ~32 min read
  • 23 sections
  • 52 code examples
🔴
Operating enterprise systemsProcedural development with PL/SQLHint-based tuningManaging high-volume transactions

Related frameworks & environments

💾Core SQL→☕Java→🗄️Spring JPA→

Contents

0 / 26
  1. How to use this guide
  2. Architecture diagrams
  3. Oracle architecture (SGA/PGA)
  4. Installation — container · RPM · full install
  5. Build — creating the database and initial setup
  6. Tablespaces & user management
  7. Oracle privilege architecture & security
  8. Oracle NLS character sets and storage
  9. Choosing a character set
  10. Multibyte text and column length (BYTE · CHAR)
  11. NLS_LANG and diagnosing garbled text
  12. PL/SQL (Procedure, Function, Trigger)
  13. Row limitation & paging
  14. Optimizer hints & execution plans
  15. Hint types and when to use them
  16. Oracle tuning strategy
  17. Locks and concurrency
  18. Diagnosing and clearing lock waits
  19. Deadlocks (ORA-00060)
  20. Migration — choosing a method and Data Pump
  21. Migrating to a different character set
  22. Verifying the migration and cutting over
  23. Oracle design
  24. Operating standards
  25. Verification strategy
  26. Frequently asked questions
Contents 26 sections
  1. How to use this guide
  2. Architecture diagrams
  3. Oracle architecture (SGA/PGA)
  4. Installation — container · RPM · full install
  5. Build — creating the database and initial setup
  6. Tablespaces & user management
  7. Oracle privilege architecture & security
  8. Oracle NLS character sets and storage
  9. Choosing a character set
  10. Multibyte text and column length (BYTE · CHAR)
  11. NLS_LANG and diagnosing garbled text
  12. PL/SQL (Procedure, Function, Trigger)
  13. Row limitation & paging
  14. Optimizer hints & execution plans
  15. Hint types and when to use them
  16. Oracle tuning strategy
  17. Locks and concurrency
  18. Diagnosing and clearing lock waits
  19. Deadlocks (ORA-00060)
  20. Migration — choosing a method and Data Pump
  21. Migrating to a different character set
  22. Verifying the migration and cutting over
  23. Oracle design
  24. Operating standards
  25. Verification strategy
  26. Frequently asked questions

How to use this guide

How to read it

Understanding Oracle as a real-world workflow

Learn the structure and key practical uses of Oracle Database, widely used in large enterprise environments such as finance and manufacturing. Covers tablespace management, writing PL/SQL, hint-based execution plan tuning, and the distinctive architecture of Oracle. Rather than listing concepts, this guide is organized so you can follow the content in the order you have to make decisions on a real project.

Key perspective

Databases

Alongside query syntax, look at schema design, indexes, transactions, permissions, and backups from an operations perspective.

Operating enterprise systemsProcedural development with PL/SQLHint-based tuningManaging high-volume transactions

Architecture diagrams

To make what you read stick, it helps to capture the flow as a picture first. The two diagrams below are reference maps you can keep coming back to while learning Oracle.

Learning flow

Rendering diagram…

Architecture view

Rendering diagram…

Oracle architecture (SGA/PGA)

When you first open Oracle, the big picture matters more than individual commands. This section starts with the problems the concepts ahead were created to solve.

In Oracle the database (the physical files) and the instance (memory structures + background processes) are separate. Memory is divided into the SGA (System Global Area), shared by all users, and the PGA (Program Global Area), allocated individually per session.
Memory areaComponentsMain role
SGA (shared memory)Database Buffer Cache, Shared Pool, Redo Log BufferCaching data blocks, caching SQL parse results, buffering transaction logs
PGA (private memory)Sort Area, Hash Area, Session InformationPerforming sorts, hash join space, holding session variables and cursor information

Installation — container · RPM · full install

Here you look at Installation — container · RPM · full install alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

There are three ways to install Oracle Database, depending on the purpose. A container is the fastest for learning and development, an RPM install puts a standard configuration on a single server, and a full install (runInstaller) is for production servers where you decide the directory layout and options yourself.

Choose a long-term support release. The most widely used in production today is 19c, and the next long-term support release is the 23ai line. Oracle Database Free, which costs nothing, belongs to the 23ai line and has limits on CPU, memory, and data size, so it is for learning and development.

1. Starting with a container (Oracle Database Free)BASH
# Create a volume so the data lives outside the container
docker volume create oracle-data

docker run -d --name oracle-free \
  -p 1521:1521 \
  -e ORACLE_PWD='ChangeMe_1234' \
  -e ORACLE_CHARACTERSET=AL32UTF8 \
  -v oracle-data:/opt/oracle/oradata \
  container-registry.oracle.com/database/free:latest

# The first start creates the database, so it takes a few minutes. It is ready when this line appears
docker logs -f oracle-free
# DATABASE IS READY TO USE!

# Connect: to FREEPDB1 (the PDB) inside FREE (the CDB)
docker exec -it oracle-free sqlplus system/ChangeMe_1234@//localhost:1521/FREEPDB1
2. RPM install (Oracle Linux, 23ai Free as the example)BASH
# Preinstall package: sets up the oracle user and groups, kernel parameters, and resource limits
sudo dnf -y install oracle-database-preinstall-23ai

# Install the RPM downloaded from the Oracle site
sudo dnf -y localinstall oracle-database-free-23ai-*.rpm

# Create the database: asks for the SYS · SYSTEM password and creates the listener and database (FREE, FREEPDB1)
sudo /etc/init.d/oracle-free-23ai configure

# Start automatically at boot
sudo systemctl enable oracle-free-23ai
3. Full install (19c, automated with a response file)BASH
# (root) Preinstall package and directories
sudo dnf -y install oracle-database-preinstall-19c
sudo mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1 /u02/oradata
sudo chown -R oracle:oinstall /u01 /u02

# (oracle) In 19c the installation files are unzipped straight into ORACLE_HOME and installed in place
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
unzip -q LINUX.X64_193000_db_home.zip -d $ORACLE_HOME

# Install the software only (the database is created with dbca in the next step)
cd $ORACLE_HOME
./runInstaller -silent -responseFile $ORACLE_HOME/install/response/db_install.rsp \
  oracle.install.option=INSTALL_DB_SWONLY \
  UNIX_GROUP_NAME=oinstall \
  INVENTORY_LOCATION=/u01/app/oraInventory \
  ORACLE_BASE=$ORACLE_BASE \
  oracle.install.db.InstallEdition=EE \
  oracle.install.db.OSDBA_GROUP=dba \
  oracle.install.db.OSOPER_GROUP=dba \
  oracle.install.db.OSBACKUPDBA_GROUP=dba \
  oracle.install.db.OSDGDBA_GROUP=dba \
  oracle.install.db.OSKMDBA_GROUP=dba \
  oracle.install.db.OSRACDBA_GROUP=dba \
  DECLINE_SECURITY_UPDATES=true

# (root) Run the two scripts the installer names when it finishes
sudo /u01/app/oraInventory/orainstRoot.sh
sudo $ORACLE_HOME/root.sh
Environment variables for the oracle user (~/.bash_profile)BASH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export ORACLE_SID=ORCLCDB
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
MethodWhen to useCharacteristics
Container (Oracle Database Free)Learning, local development, CI testsStarts with one command. Has resource and size limits.
RPM installA single server on Oracle Linux or the RHEL familyThe preinstall package does the OS setup for you. Paths and configuration are fixed to standard values.
Full install (runInstaller)Production servers, RAC, when paths and options are yours to decideYou choose the ORACLE_HOME location and components. Automated with a response file.

Tip

  • The character set is decided when the database is created and is very hard to change later. Decide it before installing. New databases use AL32UTF8 (see the character set guide below).
  • Without a volume attached, the data disappears with the container when it is removed.
  • The passwords in these examples are placeholders. In a real environment, pass them through a file or a secret so they do not stay in the command history.
  • On production servers, apply the latest Release Update (RU) patch right after installing. The base installation files are the version at release and lack security patches.
  • Package and service names contain the version (for example, 23ai). They differ with the version you install, so check the name of the file you downloaded and the installation guide for that version.
  • Oracle Database needs a license depending on the edition and options. Other than the Free edition, check the license terms before use.

Build — creating the database and initial setup

Here you look at Build — creating the database and initial setup alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

After installing the software, you create the database, open the connection path (the listener), and apply the settings needed for operations. Recent versions use the multitenant architecture: a container database (CDB) holds pluggable databases (PDBs), which are the databases applications actually use (from 21c this is the only supported architecture). Applications connect with the service name of the PDB, not the CDB.

Create the database with dbca. It can be done through the GUI, but keeping it as a command lets you build the same configuration again.

1. Create the database (dbca)BASH
dbca -silent -createDatabase \
  -templateName General_Purpose.dbc \
  -gdbname ORCLCDB -sid ORCLCDB \
  -createAsContainerDatabase true \
  -numberOfPDBs 1 -pdbName ORCLPDB1 \
  -characterSet AL32UTF8 \
  -nationalCharacterSet AL16UTF16 \
  -datafileDestination /u02/oradata \
  -storageType FS \
  -memoryPercentage 40 \
  -emConfiguration NONE \
  -sysPassword 'ChangeMe_1234' \
  -systemPassword 'ChangeMe_1234' \
  -pdbAdminPassword 'ChangeMe_1234'
2. Start and check the listenerBASH
lsnrctl start
lsnrctl status
# Service "orclpdb1" has 1 instance(s).   ← the PDB service must be listed

# Check the connection (Easy Connect: //host:port/service_name)
sqlplus system/ChangeMe_1234@//localhost:1521/ORCLPDB1
3. Check the PDB state and open it automaticallySQL
-- Connected to the CDB with sqlplus / as sysdba
SHOW PDBS;
--  CON_ID CON_NAME   OPEN MODE   RESTRICTED
--       2 PDB$SEED   READ ONLY   NO
--       3 ORCLPDB1   READ WRITE  NO

-- Open the PDB if it is MOUNTED
ALTER PLUGGABLE DATABASE ORCLPDB1 OPEN;

-- Have the PDB open automatically when the instance restarts
ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE;

-- Move into the PDB to work
ALTER SESSION SET CONTAINER = ORCLPDB1;
4. Application tablespace and userSQL
ALTER SESSION SET CONTAINER = ORCLPDB1;

CREATE TABLESPACE ts_app_data
  DATAFILE '/u02/oradata/ORCLCDB/ORCLPDB1/ts_app_data01.dbf' SIZE 1G
  AUTOEXTEND ON NEXT 256M MAXSIZE 16G;

CREATE USER app_user IDENTIFIED BY "ChangeMe_1234"
  DEFAULT TABLESPACE ts_app_data
  QUOTA UNLIMITED ON ts_app_data;

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO app_user;
5. Archive log mode (required for production databases)SQL
-- sqlplus / as sysdba
ARCHIVE LOG LIST;
-- Database log mode   No Archive Mode   ← point-in-time recovery is impossible in this state

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ARCHIVE LOG LIST;
-- Database log mode   Archive Mode
6. First backup (RMAN)BASH
rman target /

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> LIST BACKUP SUMMARY;
Build checklistWhat to check
Character setNLS_CHARACTERSET is the intended value (AL32UTF8) — hard to change once created.
Connection pathThe PDB service is registered with the listener, and port 1521 is open in the firewall only where needed.
PDB auto-openSAVE STATE was run — without it the PDB stays closed after a restart.
Archive log modeA production database is in Archive Mode and there is enough space for archive logs.
BackupRMAN backups run on a schedule and a restore has actually been tried once.
AccountsSYS · SYSTEM passwords were changed, and the application connects with a dedicated account.
Memory and storageSGA · PGA sizes fit the server memory, and data file autoextend has an upper limit.
PatchesThe latest Release Update is applied.

Tip

  • Do not put application data in the SYSTEM tablespace. Create a separate tablespace and account for the application.
  • In archive log mode, the database stops when the archive log area fills up. Decide a policy for deleting old logs after backup along with it.
  • A backup is not a backup until you have restored from it. Practice restoring to another server once during the build.
  • Application connection strings use the service name (//host:1521/ORCLPDB1), not the SID.

Tablespaces & user management

Here you look at Tablespaces & user management alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

Oracle manages data in units of tablespaces, which are logical storage spaces. Create a tablespace by specifying physical data files (.dbf), then create a user and assign the tablespace to it.
admin.sqlSQL
-- Create a tablespace
CREATE TABLESPACE ts_app_data
DATAFILE '/u01/app/oracle/oradata/XE/ts_app_data.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G;

-- Create a user and grant privileges
CREATE USER app_user IDENTIFIED BY password123
DEFAULT TABLESPACE ts_app_data;

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_user;
ALTER USER app_user QUOTA UNLIMITED ON ts_app_data;

Oracle privilege architecture & security

Here you look at Oracle privilege architecture & security alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

Oracle privileges are divided into system privileges, for creating/managing system resources, and object privileges, for accessing specific schema objects. Role-based access control (RBAC) keeps security levels consistent even in environments with a great many users.
oracle_security.sqlSQL
-- 1. Check my privileges and roles through the dictionary views
SELECT * FROM USER_SYS_PRIVS; -- system privileges granted to me
SELECT * FROM USER_TAB_PRIVS; -- object privileges granted to me
SELECT * FROM USER_ROLE_PRIVS; -- roles granted to me

-- 2. Grant the main predefined roles
-- CONNECT: the right to connect to the database (CREATE SESSION)
-- RESOURCE: basic object creation rights (CREATE TABLE, SEQUENCE, and so on)
-- DBA: all database administration rights
GRANT CONNECT, RESOURCE TO app_user;

-- 3. The risk of 'ANY' privileges and how they work
-- A privilege with the ANY keyword can run the command on every object regardless of owner, which is extremely dangerous.
-- Example: SELECT ANY TABLE can read the tables of every user except administrator schemas such as SYS and SYSTEM.
GRANT SELECT ANY TABLE TO app_user;

-- 4. Revoke the ANY privilege and roles
REVOKE SELECT ANY TABLE FROM app_user;

Tip

  • ANY privileges (e.g. SELECT ANY TABLE, DROP ANY TABLE) are a prime target of security audits, so grant them only to the absolute minimum in production.
  • Since Oracle 12c there are Common Roles and Local Roles, and account privilege design has to fit the multitenant (CDB/PDB) environment.

Oracle NLS character sets and storage

Here you look at Oracle NLS character sets and storage alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

Oracle Database supports a dual encoding structure: NLS_CHARACTERSET, the standard for data storage (VARCHAR2, CLOB, and so on), and NLS_NCHAR_CHARACTERSET, an auxiliary character set for multilingual storage (NVARCHAR2, NCLOB, and so on). You also have to define the distinction between byte semantics and character semantics on columns correctly to prevent multi-byte text truncation and ORA-12899 errors.
oracle_charset.sqlSQL
-- 1. Check the database character set and parameters
SELECT * FROM NLS_DATABASE_PARAMETERS 
WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET', 'NLS_LENGTH_SEMANTICS');
-- AL32UTF8 (Unicode, 3 bytes per Hangul character) or KO16MSWIN949 (precomposed, 2 bytes per Hangul character) is typical.

-- 2. Check the session and client character set state
SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';

-- 3. The difference between BYTE semantics and CHAR semantics, and applying them
-- BYTE semantics (default): VARCHAR2(10 BYTE) -> under AL32UTF8, at most 3 Hangul characters can be stored
CREATE TABLE emp_byte (
    emp_name VARCHAR2(10 BYTE)
);

-- CHAR semantics: VARCHAR2(10 CHAR) -> stores 10 characters regardless of byte count (10 Hangul characters)
CREATE TABLE emp_char (
    emp_name VARCHAR2(10 CHAR)
);

-- 4. Compare the byte length and character count of a string
SELECT 
    LENGTH('한글에이전트') AS char_len,     -- character count (6 characters)
    LENGTHB('한글에이전트') AS byte_len   -- actual bytes on disk (6*3=18 Bytes under AL32UTF8, 6*2=12 Bytes under KO16MSWIN949)
FROM DUAL;

Tip

  • The global standard encoding for Oracle is AL32UTF8, and when migrating to AL32UTF8 from KO16MSWIN949, the precomposed encoding of legacy Korean Windows environments, the stored bytes of existing Hangul data expand 1.5 times (2B -> 3B), so an impact analysis beforehand is essential.
  • When designing tables, even if the NLS_LENGTH_SEMANTICS parameter is set to BYTE, specifying VARCHAR2(N CHAR) explicitly is strongly recommended to guarantee compatibility in multilingual environments.

Choosing a character set

Here you look at Choosing a character set alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

Oracle has two character sets. The database character set (NLS_CHARACTERSET) is used for CHAR, VARCHAR2, and CLOB columns, SQL statements, and object names, and the national character set (NLS_NCHAR_CHARACTERSET) is used only for NCHAR, NVARCHAR2, and NCLOB columns.

Create new databases with AL32UTF8. It covers all of Unicode, so every language, CJK ideographs, and emoji can be stored, and Oracle has used it as the default since 12.2. Older systems often still run a legacy single-language character set (the examples below use KO16MSWIN949, the Windows code page for Korean), and there are characters such a character set cannot store.

Check the character set of your databaseSQL
SELECT parameter, value
FROM   nls_database_parameters
WHERE  parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET', 'NLS_LENGTH_SEMANTICS');

-- PARAMETER                VALUE
-- NLS_CHARACTERSET         AL32UTF8      ← the character set used for VARCHAR2 · CLOB
-- NLS_NCHAR_CHARACTERSET   AL16UTF16     ← the character set used for NVARCHAR2 · NCLOB
-- NLS_LENGTH_SEMANTICS     BYTE          ← the 10 in VARCHAR2(10) means bytes
See how a character is actually storedSQL
-- The second argument 1016 to DUMP: show hexadecimal and print the character set name
SELECT DUMP('가', 1016) FROM dual;

-- AL32UTF8 database:      Typ=96 Len=3 CharacterSet=AL32UTF8: ea,b0,80
-- KO16MSWIN949 database:  Typ=96 Len=2 CharacterSet=KO16MSWIN949: b0,a1
Character setSize of one Korean characterWhat it can holdVerdict
AL32UTF83 bytesAll of Unicode (every script, CJK ideographs, emoji)The standard for new databases. Use this.
KO16MSWIN9492 bytesWindows code page 949 — 11,172 Korean syllables and KS ideographsKept on existing systems. Cannot store emoji or other languages.
KO16KSC56012 bytesKS X 1001 — 2,350 Korean syllablesCannot store less common syllables such as "똠" or "쀍". Do not use it for anything new.
UTF83 bytesAn old Unicode version (stores supplementary characters in 6 bytes)For backward compatibility. Use AL32UTF8.
US7ASCII · WE8ISO8859P1—English · Western European charactersMust not be used for other scripts. Corrupted text ends up looking "as if it were stored".

Tip

  • Use the exact character set name: KO16MSWIN949. It is often shortened to "MS949" or "KO16MS949", but those are not valid setting values.
  • Leave the national character set at its default, AL16UTF16. When the database character set is AL32UTF8 there is almost no reason to use NVARCHAR2.
  • In multitenant, a CDB created as AL32UTF8 can hold PDBs with other character sets (12.2 and later). If the CDB uses another character set, every PDB must match it. Create the CDB as AL32UTF8.
  • Text that displays correctly is not proof that it was stored correctly. When in doubt, look at the stored bytes with DUMP.

Multibyte text and column length (BYTE · CHAR)

Here you look at Multibyte text and column length (BYTE · CHAR) alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

In Oracle, the 10 in VARCHAR2(10) is by default a number of bytes, not characters. In AL32UTF8 a Korean character takes 3 bytes (accented Latin letters take 2, emoji 4), so VARCHAR2(10) holds only three Korean characters. Inserting a fourth raises ORA-12899: value too large for column.

To set the length in characters, state CHAR explicitly, as in VARCHAR2(10 CHAR). The meaning "10 characters" then holds whatever the character set, so declaring new tables with CHAR is recommended.

The difference between BYTE and CHAR (on an AL32UTF8 database)SQL
CREATE TABLE t_byte (name VARCHAR2(10));        -- 10 bytes
CREATE TABLE t_char (name VARCHAR2(10 CHAR));   -- 10 characters

INSERT INTO t_byte VALUES ('홍길동');            -- 3 characters, 9 bytes: succeeds
INSERT INTO t_byte VALUES ('홍길동님');          -- 4 characters, 12 bytes
-- ORA-12899: value too large for column "T_BYTE"."NAME" (actual: 12, maximum: 10)

INSERT INTO t_char VALUES ('홍길동님');          -- 4 characters: succeeds

-- Character count and byte count
SELECT LENGTH('홍길동님') AS chars, LENGTHB('홍길동님') AS bytes FROM dual;
-- CHARS  BYTES
--     4     12
Check whether existing columns are BYTE or CHAR, and change themSQL
-- CHAR_USED: B = byte semantics, C = character semantics
SELECT table_name, column_name, data_length, char_length, char_used
FROM   user_tab_columns
WHERE  data_type = 'VARCHAR2'
ORDER  BY table_name, column_id;

-- Switch to character semantics (the data is untouched; only the allowed length grows)
ALTER TABLE t_byte MODIFY (name VARCHAR2(10 CHAR));
Change the default to CHAR in a sessionSQL
-- Tables created later in this session treat VARCHAR2(10) as 10 characters
ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR;

-- Columns that already exist are not affected
DeclarationKorean in AL32UTF8Korean in KO16MSWIN949English letters · digits
VARCHAR2(10) or VARCHAR2(10 BYTE)3 characters (9 bytes)5 characters (10 bytes)10 characters
VARCHAR2(10 CHAR)10 characters (30 bytes)10 characters (20 bytes)10 characters
VARCHAR2(4000 CHAR)At most 1,333 characters — capped by the 4,000-byte limitAt most 2,000 characters4,000 characters

Tip

  • The maximum size of a VARCHAR2 is 4,000 bytes regardless of the character count. Even declared as VARCHAR2(4000 CHAR), AL32UTF8 holds at most 1,333 Korean characters. For longer text, use a CLOB or look at MAX_STRING_SIZE=EXTENDED.
  • Match the unit of the input length limit in the UI to the unit of the column. If the UI says "20 characters" and the column is VARCHAR2(20 BYTE), the 7th Korean character already fails.
  • Changing NLS_LENGTH_SEMANTICS to CHAR for the whole database (at system level) is not recommended, because patches and internal scripts assume BYTE. State CHAR explicitly in the scripts that create tables.
  • Emoji and some ideographs take 4 bytes in AL32UTF8. In code that computes length in bytes, do not hard-code "3 bytes per character".

NLS_LANG and diagnosing garbled text

Here you look at NLS_LANG and diagnosing garbled text alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

Most garbled-text problems come from the client settings, not the database. Oracle converts the characters a client sends into the database character set before storing them, and it uses the NLS_LANG environment variable to decide "which encoding did the client send".

There is one core rule. The character set in NLS_LANG must match the encoding the client (terminal, file) actually uses, not the character set of the database. Even when the database is AL32UTF8, if you work in a Windows command prompt on a legacy code page (CP949 for Korean, CP1252 for Western European), NLS_LANG has to name that code page (KO16MSWIN949, WE8MSWIN1252), and Oracle does the conversion.

Setting NLS_LANG (language_territory.characterset)BASH
# Linux · macOS terminal (UTF-8)
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

# Windows command prompt on a legacy code page
set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252     # code page 1252 (Western European)
set NLS_LANG=KOREAN_KOREA.KO16MSWIN949         # code page 949 (Korean)

# On Windows with the terminal switched to UTF-8
chcp 65001
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
Telling corrupted data from a display problemSQL
-- Whatever the screen shows, look at the stored bytes
SELECT name, DUMP(name, 1016) AS stored FROM customers WHERE id = 1;

-- Correct ("가" in AL32UTF8):   Len=3 ...: ea,b0,80
-- Already corrupted on insert:  Len=2 ...: c2,bf     ← "¿" (could not be converted, stored as the replacement character; 3f "?" in some character sets)
-- Double conversion:            Len=6 ...: c3,aa,c2,b0,c2,80   ← UTF-8 bytes converted to UTF-8 again
SymptomCauseFix
A query shows "¿" or "?" but DUMP is correctThe NLS_LANG or terminal encoding of the querying client does not match.Set NLS_LANG to the real encoding of the terminal. The data is fine.
DUMP shows c2,bf (¿) or 3f (?) storedOn insert the character did not exist in the database character set, or NLS_LANG was wrong and conversion failed.It has to be loaded again from the source. Once stored as the replacement character, the original cannot be recovered.
Readable in one program but garbled in anotherNLS_LANG was set equal to the database character set and the data went in without conversion. It is readable only under the same settings as the program that inserted it.Insert again with the correct NLS_LANG. Data already accumulated needs a conversion job.
Text shows up mixed with Western characters, as in "ê°€"UTF-8 data was interpreted as another character set (double conversion).Find the step where the encoding was given wrongly. Check the file encoding and NLS_LANG together.
Only non-ASCII text is garbled when a SQL file runsThe encoding of the script file and NLS_LANG differ.If the file is saved as UTF-8, set NLS_LANG to AL32UTF8 as well.
ORA-12899 (when entering multibyte text)The column uses byte semantics, so the text takes more bytes than expected.Switch the column to character semantics (the section above).
An emoji turns into "¿"The database character set is a legacy one such as KO16MSWIN949 and cannot hold emoji.Migrate to an AL32UTF8 database.

Tip

  • The advice to always set NLS_LANG equal to the database character set is wrong. When the two match, Oracle neither converts nor validates and stores the bytes it receives as they are, so corrupted data piles up silently whenever the client's real encoding differs.
  • The JDBC Thin driver does not use NLS_LANG. The driver converts between Java strings (Unicode) and the database character set. If text is garbled in a Java application, check the encoding of file reads, HTTP requests, and log output rather than NLS_LANG.
  • On Windows, NLS_LANG may also be set in the registry. The environment variable takes precedence over the registry.
  • Tools such as SQL Developer and DBeaver use JDBC, so NLS_LANG does not affect them. sqlplus, Data Pump, and SQL*Loader use NLS_LANG.

PL/SQL (Procedure, Function, Trigger)

Here you look at PL/SQL (Procedure, Function, Trigger) alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

PL/SQL is the Oracle programming language that combines procedural language elements with SQL. Processing complex business logic in bulk inside the database can cut network overhead dramatically.
plsql_sample.sqlSQL
-- Example procedure (raising an employee salary)
CREATE OR REPLACE PROCEDURE raise_salary (
    p_emp_id IN NUMBER,
    p_rate   IN NUMBER
) AS
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_rate)
    WHERE emp_id = p_emp_id;
    
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found.');
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Row limitation & paging

Here you look at Row limitation & paging alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

In Oracle 11g and earlier, paging was done with a three-level query of inline views using ROWNUM, but from Oracle 12c on the OFFSET FETCH clause, in the standard SQL style, is officially supported.
paging.sqlSQL
-- Standard paging in 12c and later (recommended)
SELECT emp_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- ROWNUM paging for 11g and earlier (for legacy maintenance)
SELECT emp_name, salary
FROM (
    SELECT a.*, ROWNUM rnum
    FROM (
        SELECT emp_name, salary
        FROM employees
        ORDER BY salary DESC
    ) a
    WHERE ROWNUM <= 20
)
WHERE rnum > 10;

Optimizer hints & execution plans

Here you look at Optimizer hints & execution plans alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

The Oracle optimizer decides the best path through cost estimation, but when it takes an inefficient path because of stale statistics and the like, the developer can force the execution path directly with hints.
hints.sqlSQL
-- Applying the /*+ INDEX(table-or-alias index_name) */ hint
SELECT /*+ INDEX(e idx_emp_salary) */ emp_name, salary
FROM employees e
WHERE salary > 50000;

-- Hint forcing a hash join
SELECT /*+ USE_HASH(e d) */ e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Tip

  • Write a hint by adding + to the comment marker, and when the table has an alias, the alias must be used inside the hint for it to work.
  • If the statistics (DBMS_STATS) are not valid, a hint is ignored or works inefficiently, so statistics management has to come first.

Hint types and when to use them

Here you look at Hint types and when to use them alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

A hint is a comment that tells the optimizer "execute it this way". It is written as /*+ … */ right after SELECT, UPDATE, or DELETE. Oracle has many kinds of hints and is a database where they are used often in practice.

Even so, a hint is a last resort. A hint is a fixed instruction fitted to today's data, so the optimizer cannot switch to a better plan when the data grows or its distribution changes. Fix the statistics, the query, and the indexes first, and use a hint only when the optimizer still chooses wrongly.

Rules for writing hintsSQL
-- If the table has an alias, use the alias in the hint too
SELECT /*+ INDEX(o ix_orders_customer) */ o.id, o.amount
FROM   orders o
WHERE  o.customer_id = 42;

-- Specify join order and method together
SELECT /*+ LEADING(c o) USE_NL(o) INDEX(o ix_orders_customer) */ c.name, o.amount
FROM   customers c
JOIN   orders o ON o.customer_id = c.id
WHERE  c.grade = 'gold';

-- Wrong: the table has an alias but the hint uses the table name → ignored without an error
SELECT /*+ INDEX(orders ix_orders_customer) */ o.id FROM orders o WHERE o.customer_id = 42;
Check that a hint was actually appliedSQL
EXPLAIN PLAN FOR
SELECT /*+ INDEX(o ix_orders_customer) */ o.id FROM orders o WHERE o.customer_id = 42;

-- 19c and later: a Hint Report appears below the plan. It lists unused hints and the reason
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC +HINT_REPORT'));
CategoryHintMeaning
Access pathFULL(t)Reads the whole table.
Access pathINDEX(t index_name)Uses the named index.
Access pathNO_INDEX(t index_name)Does not use the named index.
Access pathINDEX_FFS(t index_name)Fast full scan of the index (when the index is read instead of the table).
Join orderLEADING(a b c)Joins in the order written.
Join orderORDEREDJoins in the order of the FROM clause.
Join methodUSE_NL(b)Joins b as the inner table of a nested loop.
Join methodUSE_HASH(b)Joins b with a hash join.
Join methodUSE_MERGE(b)Joins b with a sort merge join.
Query transformationNO_MERGE(v)Does not merge the inline view into the outer query.
Query transformationUNNEST / NO_UNNESTUnnests a subquery into a join, or does not.
Query transformationPUSH_PRED(v)Pushes the outer join predicate into the view.
Parallel · loadingPARALLEL(t 4)Processes with a degree of parallelism of 4.
Parallel · loadingAPPENDLoads INSERT … SELECT with a direct path.
DiagnosticsGATHER_PLAN_STATISTICSCollects actual row counts per step (use only while tuning).
DiagnosticsOPT_PARAM('name' value)Changes an optimizer parameter for this query only.

Tip

  • When a hint has a typo or the wrong target, Oracle raises no error and silently ignores it. After adding one, always confirm in the execution plan that it was applied.
  • When adding a hint, leave a comment with the reason and the date. Years later that hint is often the very thing degrading performance.
  • With only USE_NL or USE_HASH, the optimizer still decides the join order. To get what you intend, specify LEADING along with it.
  • When the application code cannot be changed, a SQL Plan Baseline or SQL Patch fixes the plan without touching the statement.

Oracle tuning strategy

Here you look at Oracle tuning strategy alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

Oracle tuning starts by reading an execution plan that contains actual execution statistics. EXPLAIN PLAN shows only the estimated plan, so how many rows were really read is checked with DBMS_XPLAN.DISPLAY_CURSOR. The step where estimated rows (E-Rows) and actual rows (A-Rows) differ widely is where the cause lies.

1. Read the plan with actual execution statisticsSQL
-- Collect per-step statistics while executing
SELECT /*+ GATHER_PLAN_STATISTICS */ c.grade, SUM(o.amount)
FROM   orders o JOIN customers c ON c.id = o.customer_id
WHERE  o.created_at >= DATE '2026-07-01'
GROUP  BY c.grade;

-- The plan and actual statistics of the query just executed
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

-- Columns to read
--   E-Rows  rows the optimizer estimated
--   A-Rows  rows actually produced        ← suspect statistics when it differs from E-Rows by 10x or more
--   Buffers blocks read                   ← the step with a large value is the expensive one
--   A-Time  time taken up to that step
2. Check and refresh statisticsSQL
-- When statistics were gathered and whether they are stale (STALE)
SELECT table_name, num_rows, last_analyzed, stale_stats
FROM   user_tab_statistics
WHERE  table_name IN ('ORDERS', 'CUSTOMERS');

-- Gather statistics (including indexes)
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'ORDERS', cascade => TRUE);

-- A column with skewed values needs a histogram to be estimated accurately
SELECT column_name, num_distinct, histogram
FROM   user_tab_col_statistics
WHERE  table_name = 'ORDERS';
3. Find the SQL that uses the most resourcesSQL
-- Among SQL still in the shared pool, the ones that read the most blocks
SELECT *
FROM  (SELECT sql_id, executions, buffer_gets,
              ROUND(buffer_gets / NULLIF(executions, 0)) AS gets_per_exec,
              ROUND(elapsed_time / 1e6, 1)               AS elapsed_sec,
              SUBSTR(sql_text, 1, 80)                    AS sql_text
       FROM   v$sql
       ORDER  BY buffer_gets DESC)
WHERE  ROWNUM <= 10;

-- The plan of a specific SQL
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '&sql_id', format => 'ALLSTATS LAST'));
4. Use bind variablesSQL
-- Putting the value in the statement makes a different SQL per value, parsed fresh every time (hard parse)
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM orders WHERE customer_id = 43;

-- With a bind variable the plan built once is reused
SELECT * FROM orders WHERE customer_id = :customer_id;
SymptomSuspectCheck and response
E-Rows and A-Rows differ widelyStatistics are stale or a histogram is missing.Gather statistics again and check that skewed columns have histograms.
TABLE ACCESS FULL for a few rowsThere is no index, or the column is transformed.Check the WHERE clause and create the index that is needed.
Nested Loop with a very large A-RowsThe row count was underestimated and the wrong join method chosen.Fix the statistics. If that does not help, consider USE_HASH.
The same query is slow only sometimesThe suitable plan differs with the bind values.Check the value distribution and histograms. Split the query when the values behave differently.
Suddenly slow after gathering statisticsThe plan changed.Pin the previous plan with a SQL Plan Baseline and investigate the cause.
High CPU and heavy parsingHard parsing repeats because bind variables are not used.Change the application to use bind variables.
Sort · hash steps are slowThey exceed work memory and use temporary space.Reduce the rows and columns fetched and check the PGA settings.

Tip

  • AWR, ASH, and SQL Tuning Advisor need licenses for paid options of Enterprise Edition (Diagnostics Pack, Tuning Pack). Even querying them is recorded as use, so confirm the license before using them. v$sql and DBMS_XPLAN need no option.
  • The tuning order is: check statistics → fix the query → indexes → hints. Starting with a hint hides the cause.
  • Regathering statistics in production can change many plans at once. On important systems, decide when and how widely statistics are gathered.
  • The queries and explanations in this section follow the official documentation and were not verified by running them on a real Oracle database.

Locks and concurrency

Here you look at Locks and concurrency alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

A lock is the mechanism that decides the order when several sessions try to change the same data at once. Oracle takes locks automatically when DML runs and releases them all together when the transaction ends (COMMIT or ROLLBACK). Developers rarely take locks by hand, but without knowing how they work you cannot explain an incident where "the screen froze".

Oracle locking has three characteristics. First, it locks at the row level. Second, reads do not block writes and writes do not block reads, because a query reads the pre-change data kept in undo. Third, however many row locks there are, they are never escalated to a table lock. The only case of blocking is a write against a write on the same row.

DML takes row locks automaticallySQL
-- Session A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Row 1 is locked from this point. The lock is held until COMMIT

-- Session B
SELECT balance FROM accounts WHERE id = 1;                  -- answers immediately (value before the change)
UPDATE accounts SET balance = balance + 50 WHERE id = 1;    -- stops here (waiting for A)

-- Session A
COMMIT;   -- the lock is released and session B's UPDATE continues
Locking rows up front before changing them (SELECT … FOR UPDATE)SQL
-- When you read a balance, compute, and then update: lock the row at read time to block changes in between
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- If another session holds it, fail immediately instead of waiting (ORA-00054)
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- Wait at most 5 seconds (ORA-30006 after that)
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE WAIT 5;

-- Skip locked rows and take only the rows that can be locked (several workers sharing a job queue)
SELECT id FROM jobs WHERE status = 'READY' FOR UPDATE SKIP LOCKED;
SituationSession ASession BResult
Read and writeUPDATE (not committed)SELECT of the same rowNo wait. B reads the value before the change.
Write and write (different rows)UPDATE row 1UPDATE row 2No wait.
Write and write (same row)UPDATE row 1 (not committed)UPDATE row 1B waits until A commits or rolls back.
DML and DDLDML on a table (not committed)ALTER on the same tableB gets ORA-00054 (resource busy).
INSERT of the same keyINSERT PK 100 (not committed)INSERT PK 100B waits. When A commits, B gets ORA-00001 (unique constraint violated).

Tip

  • Locks are released when the transaction ends. One SQL tool window left open after an edit without a commit is a common cause of production incidents.
  • Do not call external APIs or wait for user input inside a transaction. The locks are held for that whole time.
  • FOR UPDATE waits forever by default. For a query called from a UI, add NOWAIT or WAIT n to bound the wait.
  • People often guess that a slow query is caused by locks, but in Oracle an ordinary SELECT does not wait on locks. If a query is slow, look at the execution plan first.

Diagnosing and clearing lock waits

Here you look at Diagnosing and clearing lock waits alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

Many reports of "I clicked Save and nothing happens" are lock waits. One session is holding rows without finishing, and other sessions are queued behind it. Diagnose in this order: who is waiting → who is blocking → what the blocking session was doing when it stopped.

For a waiting session, the blocking_session column of v$session shows the number of the session blocking it, and the wait event is shown as enq: TX - row lock contention.

1. Who is blocking whomSQL
SELECT w.sid            AS waiting_sid,
       w.username       AS waiting_user,
       w.event,
       w.seconds_in_wait,
       b.sid            AS blocking_sid,
       b.serial#        AS blocking_serial,
       b.username       AS blocking_user,
       b.status         AS blocking_status,   -- INACTIVE means a session sitting idle without a commit
       b.machine,
       b.program
FROM   v$session w
JOIN   v$session b ON b.sid = w.blocking_session
WHERE  w.blocking_session IS NOT NULL
ORDER  BY w.seconds_in_wait DESC;
2. The last SQL the blocking session ranSQL
SELECT s.sid, s.serial#, s.status, s.last_call_et AS idle_seconds, q.sql_text
FROM   v$session s
LEFT   JOIN v$sql q ON q.sql_id = NVL(s.sql_id, s.prev_sql_id) AND q.child_number = 0
WHERE  s.sid = :blocking_sid;
3. Which tables are lockedSQL
SELECT o.owner, o.object_name, l.session_id AS sid, l.oracle_username,
       DECODE(l.locked_mode, 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share',
                             5, 'Share Row Exclusive', 6, 'Exclusive') AS lock_mode
FROM   v$locked_object l
JOIN   dba_objects o ON o.object_id = l.object_id
ORDER  BY o.object_name;
4. Kill the blocking session (last resort)SQL
-- Format 'SID,SERIAL#'. Every uncommitted change of that session is rolled back
ALTER SYSTEM KILL SESSION '123,45678' IMMEDIATE;
Lock modeNumberWhen it is taken
Row Share (RS)2SELECT … FOR UPDATE (shown as Row Exclusive in some versions)
Row Exclusive (RX)3INSERT, UPDATE, DELETE, MERGE — the table lock of ordinary DML
Share (S)4LOCK TABLE … IN SHARE MODE, changes to the parent table of an unindexed foreign key
Share Row Exclusive (SRX)5LOCK TABLE … IN SHARE ROW EXCLUSIVE MODE
Exclusive (X)6Most DDL, LOCK TABLE … IN EXCLUSIVE MODE. Row locks (TX) are also this mode.

Tip

  • Before killing a session, check what it was doing. If a large batch was nearly done, the rollback after the kill can take even longer.
  • When the blocking session is INACTIVE and has been idle a long time, the application usually returned the connection to the pool without committing, or someone edited data in a SQL tool and did not commit.
  • When DDL fails with ORA-00054, ALTER SESSION SET DDL_LOCK_TIMEOUT = 30; makes the DDL wait for the lock.
  • When the same lock wait keeps recurring, do not stop at killing the session; find and fix the code that holds the transaction open for long.

Deadlocks (ORA-00060)

Here you look at Deadlocks (ORA-00060) alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

A deadlock is two sessions each waiting for a lock the other holds. A waits for B to finish and B waits for A, so it never resolves unless someone steps in. That is the difference from an ordinary lock wait, which clears when the session in front commits.

Oracle detects deadlocks automatically and returns ORA-00060: deadlock detected while waiting for resource to one of the sessions. There is a catch: Oracle cancels only the one statement that got the error and leaves the rest of that transaction's work and locks in place. The other session can proceed only after the application that received the error rolls back.

How a deadlock formsSQL
-- Session A                                     -- Session B
UPDATE accounts SET balance = balance - 100
WHERE  id = 1;        -- holds row 1
                                                 UPDATE accounts SET balance = balance - 50
                                                 WHERE  id = 2;        -- holds row 2

UPDATE accounts SET balance = balance + 100
WHERE  id = 2;        -- waits for B
                                                 UPDATE accounts SET balance = balance + 50
                                                 WHERE  id = 1;        -- waits for A → deadlock

-- A moment later session A gets the error
-- ORA-00060: deadlock detected while waiting for resource
-- Only A's second UPDATE is cancelled. The first UPDATE and the lock on row 1 remain

ROLLBACK;             -- B's UPDATE proceeds only after A rolls back
Prevention 1 — always lock in the same orderSQL
-- When changing two accounts, make it a rule to lock the smaller ID first.
-- If every session follows the same order, sessions never wait on each other crosswise
SELECT id, balance
FROM   accounts
WHERE  id IN (:from_id, :to_id)
ORDER  BY id
FOR UPDATE;

UPDATE accounts SET balance = balance - :amount WHERE id = :from_id;
UPDATE accounts SET balance = balance + :amount WHERE id = :to_id;
COMMIT;
Prevention 2 — find foreign keys without an indexSQL
-- When the foreign key column of a child table has no index, deleting a parent row or changing its key
-- locks the whole child table, which causes deadlocks and lock waits.
-- (a simple check for single-column foreign keys)
SELECT c.table_name, cc.column_name, c.constraint_name
FROM   user_constraints  c
JOIN   user_cons_columns cc ON cc.constraint_name = c.constraint_name
WHERE  c.constraint_type = 'R'
AND    NOT EXISTS (
         SELECT 1
         FROM   user_ind_columns ic
         WHERE  ic.table_name      = c.table_name
         AND    ic.column_name     = cc.column_name
         AND    ic.column_position = cc.position
       )
ORDER  BY c.table_name;

-- Create an index on the columns it finds
CREATE INDEX ix_orders_customer_id ON orders (customer_id);
Application — on a deadlock, roll back and retryJAVA
// ORA-00060 arrives as error code 60
for (int attempt = 1; attempt <= 3; attempt++) {
    try {
        transfer(conn, fromId, toId, amount);   // several UPDATEs
        conn.commit();
        return;
    } catch (SQLException e) {
        conn.rollback();                        // always roll back to release the remaining locks
        if (e.getErrorCode() != 60 || attempt == 3) throw e;
        Thread.sleep(50L * attempt);            // pause briefly and retry
    }
}
CauseExplanationFix
Different locking orderOne piece of code changes rows in the order A → B and another in the order B → A. The most common cause.Lock in a fixed order, such as key order.
Foreign key without an indexDeleting a parent row or changing its key takes a table lock on the child table.Create an index on the foreign key column.
Long transactionsThe longer locks are held, the more likely sessions cross each other.Keep transactions short and make no external calls inside them.
Bitmap indexes with concurrent DMLIn a bitmap index one key value locks many rows together.Do not use bitmap indexes on tables updated concurrently.
Not enough transaction slots in a blockMany transactions try to change one block at once and wait for a slot (ITL).Increase INITRANS on the table and its indexes.
Autonomous transactionsAn autonomous transaction tries to change a row held by the transaction that called it.Keep autonomous transactions away from the same rows.

Tip

  • A deadlock is recorded in the alert log and a trace file is written. The "Deadlock graph" in the trace file shows the two sessions that waited on each other and the SQL each was running. It is the first thing to read when looking for the cause.
  • Running the same statement again after ORA-00060 without rolling back repeats the same deadlock. Roll back the whole transaction and retry from the start.
  • Retrying is first aid. If deadlocks are frequent, fix the cause, such as the locking order or foreign key indexes.
  • A deadlock is not a database defect; it is a problem with the order in which the application takes locks. It cannot be removed by changing Oracle settings.

Migration — choosing a method and Data Pump

Here you look at Migration — choosing a method and Data Pump alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

There are several ways to move an Oracle database to another server or another version. The choice depends on the scope to move, the downtime you can accept, and whether the version, platform, and character set of source and target are the same.

The most widely used is Data Pump (expdp · impdp). It exports data logically and loads it again, so it works across versions, operating systems, and character sets, and you can pick schemas to move.

1. Survey the source before migratingSQL
-- Character set and version
SELECT parameter, value FROM nls_database_parameters WHERE parameter LIKE '%CHARACTERSET';
SELECT banner_full FROM v$version;

-- Size of the schemas to move
SELECT owner, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 1) AS gb
FROM   dba_segments
WHERE  owner IN ('APP_USER')
GROUP  BY owner;

-- Object counts (save these as the baseline to compare after the migration)
SELECT owner, object_type, COUNT(*) AS cnt
FROM   dba_objects
WHERE  owner IN ('APP_USER')
GROUP  BY owner, object_type
ORDER  BY owner, object_type;

-- Objects that are already invalid (to tell them apart from ones the migration breaks)
SELECT owner, object_name, object_type FROM dba_objects WHERE status = 'INVALID' AND owner IN ('APP_USER');
2. Prepare the directory object (on both source and target)SQL
-- Data Pump writes files to a directory on the server. Register the OS path as a directory object
CREATE OR REPLACE DIRECTORY dp_dir AS '/u02/dump';
GRANT READ, WRITE ON DIRECTORY dp_dir TO system;
3. Export from the source (expdp)BASH
expdp system@//source-host:1521/ORCLPDB1 \
  schemas=APP_USER \
  directory=dp_dir \
  dumpfile=app_user_%U.dmp \
  logfile=expdp_app_user.log \
  parallel=4 \
  flashback_time=systimestamp \
  exclude=statistics

# flashback_time: exports a consistent point-in-time state even if data changes during the export
# exclude=statistics: statistics are not moved; they are gathered fresh on the target
# parallel=4 and %U: export into several files at once (Enterprise Edition)
4. Import on the target (impdp)BASH
# After copying the dump files to /u02/dump on the target server

impdp system@//target-host:1521/NEWPDB \
  schemas=APP_USER \
  directory=dp_dir \
  dumpfile=app_user_%U.dmp \
  logfile=impdp_app_user.log \
  parallel=4 \
  remap_tablespace=USERS:TS_APP_DATA

# remap_tablespace: maps the source tablespace name to the target name
# remap_schema=APP_USER:NEW_USER renames the schema as well
# Always check "completed with N error(s)" at the end of the log
5. Finish up after the importSQL
-- Recompile invalid objects
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'APP_USER', compile_all => FALSE);

-- Gather optimizer statistics fresh
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'APP_USER', cascade => TRUE);
MethodWhen to useWatch out for
Data Pump (expdp · impdp)Different version · platform · character set, or moving only some schemasTakes time in proportion to data size. Changes made during the export have to be handled separately.
RMAN backup · restore, DUPLICATEMoving a whole database to the same platform and versionCannot change the character set. The whole database is the unit.
PDB unplug · plug, cloneMoving per PDB in a multitenant environmentCheck version and patch level, and character set compatibility.
Transportable TablespaceMoving very large data quicklyNeeds a period with the tablespaces read-only, and the conditions are demanding.
Replication tools such as GoldenGateWhen downtime has to be nearly zeroA separate product with its own license and setup work.

Tip

  • When the target database version is lower than the source, expdp needs the version parameter set to the target version. Going to a higher version needs nothing.
  • Rehearse once with the same procedure before the real migration. Time it and resolve the errors in the logs ahead of time.
  • Do not skip past errors such as ORA-39083, ORA-12899, and ORA-01917 (user or role does not exist) in the impdp log. The import may have finished with grants and data missing.
  • Current sequence values, database links, scheduler jobs, public synonyms, and grants received from other schemas are easily lost or misaligned in a schema-level migration. Put them on the checklist.
  • Dump files contain the business data as it is. Restrict access during transfer and storage, and delete them when the migration is done.

Migrating to a different character set (KO16MSWIN949 → AL32UTF8)

Here you look at Migrating to a different character set (KO16MSWIN949 → AL32UTF8) alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

Moving an existing database on a legacy character set to an AL32UTF8 database is the part of migration where most accidents happen. The example here is KO16MSWIN949, where each Korean character grows from 2 bytes to 3; the same applies to accented letters going from 1 byte to 2 out of a Western European character set. Data Pump converts the characters, but when a column uses byte semantics the larger data does not fit.

For example, a VARCHAR2(20) column holding 10 Korean characters (20 bytes) needs 30 bytes in AL32UTF8, and the whole row is rejected with ORA-12899. Data Pump creates the columns exactly as defined in the source, so changing settings on the target database alone does not solve it.

1. Find the affected columns ahead of time (on the source)SQL
-- Character columns with byte semantics
SELECT table_name, column_name, data_type, data_length
FROM   dba_tab_columns
WHERE  owner = 'APP_USER'
AND    data_type IN ('VARCHAR2', 'CHAR')
AND    char_used = 'B'
ORDER  BY table_name, column_id;

-- Number of rows in a given column that exceed the length after conversion
-- CONVERT computes the byte count in AL32UTF8
SELECT COUNT(*) AS overflow_rows
FROM   app_user.customers
WHERE  LENGTHB(CONVERT(name, 'AL32UTF8')) > 20;   -- 20 = byte length of the column
2. Import the structure firstBASH
impdp system@//target-host:1521/NEWPDB \
  schemas=APP_USER directory=dp_dir dumpfile=app_user_%U.dmp \
  logfile=impdp_meta.log \
  content=metadata_only \
  exclude=index,constraint,ref_constraint,trigger,statistics
3. Switch the columns to character semantics on the targetSQL
-- Generate statements that change byte-semantics VARCHAR2 · CHAR columns to the same number in characters
SELECT 'ALTER TABLE ' || owner || '.' || table_name ||
       ' MODIFY (' || column_name || ' ' || data_type || '(' || data_length || ' CHAR));' AS ddl
FROM   dba_tab_columns
WHERE  owner = 'APP_USER'
AND    data_type IN ('VARCHAR2', 'CHAR')
AND    char_used = 'B'
ORDER  BY table_name, column_id;

-- Review the generated statements, then run them
-- Example: ALTER TABLE APP_USER.CUSTOMERS MODIFY (NAME VARCHAR2(20 CHAR));
4. Import the data, then indexes and constraintsBASH
# Data only
impdp system@//target-host:1521/NEWPDB \
  schemas=APP_USER directory=dp_dir dumpfile=app_user_%U.dmp \
  logfile=impdp_data.log \
  content=data_only \
  parallel=4

# After confirming the log has no ORA-12899 and no "rows rejected", create the remaining objects
impdp system@//target-host:1521/NEWPDB \
  schemas=APP_USER directory=dp_dir dumpfile=app_user_%U.dmp \
  logfile=impdp_post.log \
  content=metadata_only \
  include=index,constraint,ref_constraint,trigger
ProblemCauseResponse
Rows are rejected with ORA-12899The larger multibyte text does not fit in a byte-semantics column.Switch the column to CHAR semantics, then load the data (the procedure above).
Long text is cut off or missingVARCHAR2 is capped at 4,000 bytes. 1,334 or more Korean characters do not fit even with CHAR.Change the column to a CLOB or look at MAX_STRING_SIZE=EXTENDED.
Index creation fails (ORA-01450)The column grew and exceeded the maximum index key length.Shorten the indexed column or use a tablespace with a larger block size.
"¿" appears after the migrationThe source already held wrongly stored data. The conversion exposed it.Confirm with DUMP on the source and correct the source data before migrating.
Length errors in the applicationThe UI and code compute length assuming a fixed number of bytes per character.Change byte calculations to character counts.
ORA-06502 in PL/SQL variablesA VARCHAR2(20) variable in code also uses byte semantics.Declare variables with CHAR semantics or %TYPE.

Tip

  • The NLS_LANG of the session running expdp and impdp does not affect the conversion. The dump file records the source character set, and impdp converts to the target character set. (The older exp · imp tools are affected by NLS_LANG.)
  • DMU (Database Migration Assistant for Unicode) from Oracle converts an existing database to Unicode in place, and before converting it scans for data that would exceed lengths or cannot be converted. Even if you are not converting in place, it is useful as a pre-check.
  • Changing the character set with the old CSALTER script or the ALTER DATABASE CHARACTER SET command is no longer supported. Do not follow old articles on the internet.
  • Switching a column to CHAR semantics makes AL32UTF8 count up to 4 bytes per character, so the maximum byte length of the column grows. Check fixed-length CHAR columns and index key lengths along with it.

Verifying the migration and cutting over

Here you look at Verifying the migration and cutting over alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

The migration is not finished when the import ends. Confirm with numbers that source and target are the same, connect the application and try it, and then cut over. The baseline for verification is the set of values taken from the source before the migration started.

1. Compare object countsSQL
-- Run on both source and target and compare the results
SELECT object_type, COUNT(*) AS cnt
FROM   dba_objects
WHERE  owner = 'APP_USER'
GROUP  BY object_type
ORDER  BY object_type;

-- Invalid objects
SELECT object_name, object_type FROM dba_objects WHERE owner = 'APP_USER' AND status = 'INVALID';
2. Compare row countsSQL
-- Generate statements that count the real rows per table (num_rows in the statistics is an estimate, so it is not used)
SELECT 'SELECT ''' || table_name || ''' AS t, COUNT(*) AS c FROM APP_USER.' || table_name || ' UNION ALL'
FROM   dba_tables
WHERE  owner = 'APP_USER'
ORDER  BY table_name;
3. Sample the multibyte dataSQL
-- The character count must match the source; the byte count depends on the character set
SELECT id, name, LENGTH(name) AS chars, LENGTHB(name) AS bytes, DUMP(name, 1016) AS stored
FROM   app_user.customers
WHERE  id IN (1, 100, 1000);

-- Rows that came in with a character that failed conversion ("¿")
SELECT COUNT(*) FROM app_user.customers WHERE name LIKE '%' || UNISTR('\00BF') || '%';
4. Sequences and constraintsSQL
-- The next value of each sequence must be greater than the largest key in its table
SELECT sequence_name, last_number FROM dba_sequences WHERE sequence_owner = 'APP_USER';

-- Constraints left disabled and indexes that are unusable
SELECT table_name, constraint_name, status FROM dba_constraints WHERE owner = 'APP_USER' AND status <> 'ENABLED';
SELECT index_name, status FROM dba_indexes WHERE owner = 'APP_USER' AND status NOT IN ('VALID', 'N/A');
StageWhat to do
RehearsalRun the whole procedure with production-sized data and record the time taken and the errors.
Before cutoverSave the object and row count baselines from the source, confirm the source backup, and write down the rollback procedure.
Stop writesStop the application or switch it to read-only so no new data reaches the source.
Run the migrationProceed in order: export → transfer → import → recompile → gather statistics, checking the log at each step.
VerifyCompare object counts, row counts, multibyte samples, sequences, and constraints with the source.
Check the applicationChange the connection string and actually run the main operations (queries, inserts, non-ASCII input, batch jobs).
Cut overPoint DNS or the connection settings at the target database.
After cutoverKeep the source read-only for a period, and watch performance and error logs for several days.

Tip

  • Decide the rollback criteria in advance. Only when "if verification is not finished by this time, roll back to the source" is settled do decisions stay steady on the day.
  • Once new data starts accumulating on the target after cutover, going back to the source becomes hard. Know until when rollback is still possible.
  • Right after a migration, execution plans can change and specific queries can slow down. Confirm that statistics were gathered and compare the plans of the main queries with the source.
  • Keep the verification queries and their results on record. If a data problem is raised later, they are the evidence of the state at migration time.

Oracle practical design

Oracle practical design is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

In Oracle design, tablespace layout, partitioning, and the design of the system/object privilege hierarchy matter. In particular, ANY privileges give arbitrary access to the resources of other schemas, so forbid them strictly and design fine-grained roles per job function.
Decision pointQuestion to askPractical guideline
BoundariesWhich parts of the Oracle code are most likely to change?Separate input/output, configuration, external integrations, and the core rules.
StateWhere is state created and where does it go away?Make the owner and lifecycle of state visible in the code.
FailureWhat does the caller receive on failure?Decide the timeout, fallback, and error contract first.

Oracle operating standards

This section covers Oracle operating standards from a practical perspective. Instead of memorizing the concept, focus on the situations in which you would reach for it.

You need monitoring of the Top Wait Events in AWR reports, SGA/PGA size tuning, and statistics management, and accounts holding the DBA and RESOURCE roles must be controlled so they are not connected all the time.

Tip

  • Analyze the Top 5 Wait Events in the AWR report
  • Check for granted ANY privileges (e.g., SELECT ANY TABLE)
  • Missing PL/SQL exception handling
  • Verify role mapping compliance from USER_ROLE_PRIVS

Oracle verification strategy

Oracle verification strategy is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

Beyond careful PL/SQL verification and checking that hints are appropriate, run security regression tests that inspect the USER_SYS_PRIVS and USER_ROLE_PRIVS dictionary views for unwanted privilege inheritance.
Quality axisHow to verifyDefinition of done
CorrectnessAutomate the normal/failure cases.The core scenarios pass reproducibly.
Regression preventionWhen fixing a bug, leave the same case behind as a test.The same failure is not deployed again.
OperabilityCheck the logs, metrics, and alerts.There is a path for tracing the cause when a problem occurs.

Oracle: frequently asked questions

How do I fetch only a few rows of a result in Oracle?

Recent versions support FETCH FIRST n ROWS ONLY. The older ROWNUM is assigned before sorting, so to take a few rows from a sorted result you have to wrap the sorted subquery and apply the ROWNUM condition outside it.

How do an empty string and NULL differ in Oracle?

Oracle treats an empty string as NULL for the VARCHAR2 type. A condition such as column = '' is therefore never true, and you have to compare with IS NULL. This often causes trouble in queries brought over from other databases.

How do I look at an execution plan?

Generate the estimated plan with EXPLAIN PLAN FOR and view it with DBMS_XPLAN.DISPLAY. You can see whether it is a full table scan or uses an index, and the join order and methods. The estimated plan can differ from actual execution, so for slow queries also look at the actual execution statistics.

← Previous guideStandard SQLNext guide →MySQL