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
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 area
Components
Main role
SGA (shared memory)
Database Buffer Cache, Shared Pool, Redo Log Buffer
Caching data blocks, caching SQL parse results, buffering transaction logs
PGA (private memory)
Sort Area, Hash Area, Session Information
Performing 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 containerdocker volume create oracle-datadocker 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 appearsdocker 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 limitssudo dnf -y install oracle-database-preinstall-23ai# Install the RPM downloaded from the Oracle sitesudo 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 bootsudo systemctl enable oracle-free-23ai
3. Full install (19c, automated with a response file)BASH
# (root) Preinstall package and directoriessudo dnf -y install oracle-database-preinstall-19csudo mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1 /u02/oradatasudo chown -R oracle:oinstall /u01 /u02# (oracle) In 19c the installation files are unzipped straight into ORACLE_HOME and installed in placeexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1unzip -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 finishessudo /u01/app/oraInventory/orainstRoot.shsudo $ORACLE_HOME/root.sh
Environment variables for the oracle user (~/.bash_profile)BASH
Starts with one command. Has resource and size limits.
RPM install
A single server on Oracle Linux or the RHEL family
The 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 decide
You choose the ORACLE_HOME location and components. Automated with a response file.
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.
lsnrctl startlsnrctl 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 sysdbaSHOW 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 MOUNTEDALTER PLUGGABLE DATABASE ORCLPDB1 OPEN;-- Have the PDB open automatically when the instance restartsALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE;-- Move into the PDB to workALTER 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 sysdbaARCHIVE LOG LIST;-- Database log mode No Archive Mode ← point-in-time recovery is impossible in this stateSHUTDOWN 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 checklist
What to check
Character set
NLS_CHARACTERSET is the intended value (AL32UTF8) — hard to change once created.
Connection path
The PDB service is registered with the listener, and port 1521 is open in the firewall only where needed.
PDB auto-open
SAVE STATE was run — without it the PDB stays closed after a restart.
Archive log mode
A production database is in Archive Mode and there is enough space for archive logs.
Backup
RMAN backups run on a schedule and a restore has actually been tried once.
Accounts
SYS · SYSTEM passwords were changed, and the application connects with a dedicated account.
Memory and storage
SGA · PGA sizes fit the server memory, and data file autoextend has an upper limit.
Patches
The latest Release Update is applied.
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 tablespaceCREATE TABLESPACE ts_app_dataDATAFILE '/u01/app/oracle/oradata/XE/ts_app_data.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G;-- Create a user and grant privilegesCREATE USER app_user IDENTIFIED BY password123DEFAULT 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 viewsSELECT * FROM USER_SYS_PRIVS; -- system privileges granted to meSELECT * FROM USER_TAB_PRIVS; -- object privileges granted to meSELECT * 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 rightsGRANT 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 rolesREVOKE SELECT ANY TABLE FROM app_user;
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 parametersSELECT * 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 stateSELECT * 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 storedCREATE 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 stringSELECT 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;
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, valueFROM nls_database_parametersWHERE 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 nameSELECT 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 set
Size of one Korean character
What it can hold
Verdict
AL32UTF8
3 bytes
All of Unicode (every script, CJK ideographs, emoji)
The standard for new databases. Use this.
KO16MSWIN949
2 bytes
Windows code page 949 — 11,172 Korean syllables and KS ideographs
Kept on existing systems. Cannot store emoji or other languages.
KO16KSC5601
2 bytes
KS X 1001 — 2,350 Korean syllables
Cannot store less common syllables such as "똠" or "쀍". Do not use it for anything new.
UTF8
3 bytes
An old Unicode version (stores supplementary characters in 6 bytes)
For backward compatibility. Use AL32UTF8.
US7ASCII · WE8ISO8859P1
—
English · Western European characters
Must not be used for other scripts. Corrupted text ends up looking "as if it were stored".
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 bytesCREATE TABLE t_char (name VARCHAR2(10 CHAR)); -- 10 charactersINSERT INTO t_byte VALUES ('홍길동'); -- 3 characters, 9 bytes: succeedsINSERT 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 countSELECT 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 semanticsSELECT table_name, column_name, data_length, char_length, char_usedFROM user_tab_columnsWHERE 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 charactersALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR;-- Columns that already exist are not affected
Declaration
Korean in AL32UTF8
Korean in KO16MSWIN949
English 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 limit
At most 2,000 characters
4,000 characters
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.
# Linux · macOS terminal (UTF-8)export NLS_LANG=AMERICAN_AMERICA.AL32UTF8# Windows command prompt on a legacy code pageset 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-8chcp 65001set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
Telling corrupted data from a display problemSQL
-- Whatever the screen shows, look at the stored bytesSELECT 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
Symptom
Cause
Fix
A query shows "¿" or "?" but DUMP is correct
The 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 (?) stored
On 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 another
NLS_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 runs
The 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.
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) ASBEGIN 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, salaryFROM employeesORDER BY salary DESCOFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;-- ROWNUM paging for 11g and earlier (for legacy maintenance)SELECT emp_name, salaryFROM ( 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) */ hintSELECT /*+ INDEX(e idx_emp_salary) */ emp_name, salaryFROM employees eWHERE salary > 50000;-- Hint forcing a hash joinSELECT /*+ USE_HASH(e d) */ e.emp_name, d.dept_nameFROM employees eINNER JOIN departments d ON e.dept_id = d.dept_id;
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 tooSELECT /*+ INDEX(o ix_orders_customer) */ o.id, o.amountFROM orders oWHERE o.customer_id = 42;-- Specify join order and method togetherSELECT /*+ LEADING(c o) USE_NL(o) INDEX(o ix_orders_customer) */ c.name, o.amountFROM customers cJOIN orders o ON o.customer_id = c.idWHERE c.grade = 'gold';-- Wrong: the table has an alias but the hint uses the table name → ignored without an errorSELECT /*+ INDEX(orders ix_orders_customer) */ o.id FROM orders o WHERE o.customer_id = 42;
Check that a hint was actually appliedSQL
EXPLAIN PLAN FORSELECT /*+ 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 reasonSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC +HINT_REPORT'));
Category
Hint
Meaning
Access path
FULL(t)
Reads the whole table.
Access path
INDEX(t index_name)
Uses the named index.
Access path
NO_INDEX(t index_name)
Does not use the named index.
Access path
INDEX_FFS(t index_name)
Fast full scan of the index (when the index is read instead of the table).
Join order
LEADING(a b c)
Joins in the order written.
Join order
ORDERED
Joins in the order of the FROM clause.
Join method
USE_NL(b)
Joins b as the inner table of a nested loop.
Join method
USE_HASH(b)
Joins b with a hash join.
Join method
USE_MERGE(b)
Joins b with a sort merge join.
Query transformation
NO_MERGE(v)
Does not merge the inline view into the outer query.
Query transformation
UNNEST / NO_UNNEST
Unnests a subquery into a join, or does not.
Query transformation
PUSH_PRED(v)
Pushes the outer join predicate into the view.
Parallel · loading
PARALLEL(t 4)
Processes with a degree of parallelism of 4.
Parallel · loading
APPEND
Loads INSERT … SELECT with a direct path.
Diagnostics
GATHER_PLAN_STATISTICS
Collects actual row counts per step (use only while tuning).
Diagnostics
OPT_PARAM('name' value)
Changes an optimizer parameter for this query only.
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 executingSELECT /*+ GATHER_PLAN_STATISTICS */ c.grade, SUM(o.amount)FROM orders o JOIN customers c ON c.id = o.customer_idWHERE o.created_at >= DATE '2026-07-01'GROUP BY c.grade;-- The plan and actual statistics of the query just executedSELECT * 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_statsFROM user_tab_statisticsWHERE 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 accuratelySELECT column_name, num_distinct, histogramFROM user_tab_col_statisticsWHERE 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 blocksSELECT *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 SQLSELECT * 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 reusedSELECT * FROM orders WHERE customer_id = :customer_id;
Symptom
Suspect
Check and response
E-Rows and A-Rows differ widely
Statistics are stale or a histogram is missing.
Gather statistics again and check that skewed columns have histograms.
TABLE ACCESS FULL for a few rows
There 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-Rows
The 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 sometimes
The 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 statistics
The plan changed.
Pin the previous plan with a SQL Plan Baseline and investigate the cause.
High CPU and heavy parsing
Hard parsing repeats because bind variables are not used.
Change the application to use bind variables.
Sort · hash steps are slow
They exceed work memory and use temporary space.
Reduce the rows and columns fetched and check the PGA settings.
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 AUPDATE accounts SET balance = balance - 100 WHERE id = 1;-- Row 1 is locked from this point. The lock is held until COMMIT-- Session BSELECT 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 ACOMMIT; -- 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 betweenSELECT 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;
Situation
Session A
Session B
Result
Read and write
UPDATE (not committed)
SELECT of the same row
No wait. B reads the value before the change.
Write and write (different rows)
UPDATE row 1
UPDATE row 2
No wait.
Write and write (same row)
UPDATE row 1 (not committed)
UPDATE row 1
B waits until A commits or rolls back.
DML and DDL
DML on a table (not committed)
ALTER on the same table
B gets ORA-00054 (resource busy).
INSERT of the same key
INSERT PK 100 (not committed)
INSERT PK 100
B waits. When A commits, B gets ORA-00001 (unique constraint violated).
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.programFROM v$session wJOIN v$session b ON b.sid = w.blocking_sessionWHERE w.blocking_session IS NOT NULLORDER 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_textFROM v$session sLEFT JOIN v$sql q ON q.sql_id = NVL(s.sql_id, s.prev_sql_id) AND q.child_number = 0WHERE 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_modeFROM v$locked_object lJOIN dba_objects o ON o.object_id = l.object_idORDER BY o.object_name;
4. Kill the blocking session (last resort)SQL
-- Format 'SID,SERIAL#'. Every uncommitted change of that session is rolled backALTER SYSTEM KILL SESSION '123,45678' IMMEDIATE;
Lock mode
Number
When it is taken
Row Share (RS)
2
SELECT … FOR UPDATE (shown as Row Exclusive in some versions)
Row Exclusive (RX)
3
INSERT, UPDATE, DELETE, MERGE — the table lock of ordinary DML
Share (S)
4
LOCK TABLE … IN SHARE MODE, changes to the parent table of an unindexed foreign key
Share Row Exclusive (SRX)
5
LOCK TABLE … IN SHARE ROW EXCLUSIVE MODE
Exclusive (X)
6
Most DDL, LOCK TABLE … IN EXCLUSIVE MODE. Row locks (TX) are also this mode.
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 BUPDATE accounts SET balance = balance - 100WHERE id = 1; -- holds row 1 UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- holds row 2UPDATE accounts SET balance = balance + 100WHERE 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 remainROLLBACK; -- 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 crosswiseSELECT id, balanceFROM accountsWHERE id IN (:from_id, :to_id)ORDER BY idFOR 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_nameFROM user_constraints cJOIN user_cons_columns cc ON cc.constraint_name = c.constraint_nameWHERE 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 findsCREATE INDEX ix_orders_customer_id ON orders (customer_id);
Application — on a deadlock, roll back and retryJAVA
// ORA-00060 arrives as error code 60for (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 }}
Cause
Explanation
Fix
Different locking order
One 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 index
Deleting a parent row or changing its key takes a table lock on the child table.
Create an index on the foreign key column.
Long transactions
The 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 DML
In 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 block
Many transactions try to change one block at once and wait for a slot (ITL).
Increase INITRANS on the table and its indexes.
Autonomous transactions
An autonomous transaction tries to change a row held by the transaction that called it.
Keep autonomous transactions away from the same rows.
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 versionSELECT parameter, value FROM nls_database_parameters WHERE parameter LIKE '%CHARACTERSET';SELECT banner_full FROM v$version;-- Size of the schemas to moveSELECT owner, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 1) AS gbFROM dba_segmentsWHERE 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 cntFROM dba_objectsWHERE owner IN ('APP_USER')GROUP BY owner, object_typeORDER 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 objectCREATE 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 serverimpdp 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
Different version · platform · character set, or moving only some schemas
Takes time in proportion to data size. Changes made during the export have to be handled separately.
RMAN backup · restore, DUPLICATE
Moving a whole database to the same platform and version
Cannot change the character set. The whole database is the unit.
PDB unplug · plug, clone
Moving per PDB in a multitenant environment
Check version and patch level, and character set compatibility.
Transportable Tablespace
Moving very large data quickly
Needs a period with the tablespaces read-only, and the conditions are demanding.
Replication tools such as GoldenGate
When downtime has to be nearly zero
A separate product with its own license and setup work.
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 semanticsSELECT table_name, column_name, data_type, data_lengthFROM dba_tab_columnsWHERE 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 AL32UTF8SELECT COUNT(*) AS overflow_rowsFROM app_user.customersWHERE LENGTHB(CONVERT(name, 'AL32UTF8')) > 20; -- 20 = byte length of the column
3. Switch the columns to character semantics on the targetSQL
-- Generate statements that change byte-semantics VARCHAR2 · CHAR columns to the same number in charactersSELECT 'ALTER TABLE ' || owner || '.' || table_name || ' MODIFY (' || column_name || ' ' || data_type || '(' || data_length || ' CHAR));' AS ddlFROM dba_tab_columnsWHERE 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 onlyimpdp 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 objectsimpdp 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
Problem
Cause
Response
Rows are rejected with ORA-12899
The 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 missing
VARCHAR2 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 migration
The 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 application
The UI and code compute length assuming a fixed number of bytes per character.
Change byte calculations to character counts.
ORA-06502 in PL/SQL variables
A VARCHAR2(20) variable in code also uses byte semantics.
Declare variables with CHAR semantics or %TYPE.
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 resultsSELECT object_type, COUNT(*) AS cntFROM dba_objectsWHERE owner = 'APP_USER'GROUP BY object_typeORDER BY object_type;-- Invalid objectsSELECT 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_tablesWHERE 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 setSELECT id, name, LENGTH(name) AS chars, LENGTHB(name) AS bytes, DUMP(name, 1016) AS storedFROM app_user.customersWHERE 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 tableSELECT sequence_name, last_number FROM dba_sequences WHERE sequence_owner = 'APP_USER';-- Constraints left disabled and indexes that are unusableSELECT 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');
Stage
What to do
Rehearsal
Run the whole procedure with production-sized data and record the time taken and the errors.
Before cutover
Save the object and row count baselines from the source, confirm the source backup, and write down the rollback procedure.
Stop writes
Stop the application or switch it to read-only so no new data reaches the source.
Run the migration
Proceed in order: export → transfer → import → recompile → gather statistics, checking the log at each step.
Verify
Compare object counts, row counts, multibyte samples, sequences, and constraints with the source.
Check the application
Change the connection string and actually run the main operations (queries, inserts, non-ASCII input, batch jobs).
Cut over
Point DNS or the connection settings at the target database.
After cutover
Keep the source read-only for a period, and watch performance and error logs for several days.
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 point
Question to ask
Practical guideline
Boundaries
Which parts of the Oracle code are most likely to change?
Separate input/output, configuration, external integrations, and the core rules.
State
Where is state created and where does it go away?
Make the owner and lifecycle of state visible in the code.
Failure
What 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.
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 axis
How to verify
Definition of done
Correctness
Automate the normal/failure cases.
The core scenarios pass reproducibly.
Regression prevention
When fixing a bug, leave the same case behind as a test.
The same failure is not deployed again.
Operability
Check 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.